Find Text And Copy Found Range
May 15, 2007
I have some EXCEL files, titled as "AAA", "BBB", "CCC", so on... Calculated values are placed in Column Z for each file. In order for me to export the files to another software, I would need to create another new EXCEL file (I call it MASS) and place all the calculated values in that new file. In that new file, the titles have to be on Column A and the calculated values on Column B.
For example, File "AAA" has 120 calculated values. In MASS, the word "AAA" has to appear on Column A a number of 120 times and the 120 calculated values are then placed on Column B. Each value must correspond to the word "AAA". The same procedure goes for "BBB", in which the word "BBB" is placed on Column A after the end of "AAA". What I want to achieve is by using a macro that will look up the title of the Active Worksheet and then scan Column A in MASS. If the title matches whatever is in Column A, then the macro will paste the corresponding calculated values onto Column B automatically.
For example, for file "GGG", the macro will look up the word "GGG", scan through Column A in MASS for the start of the word "GGG" and paste the calculated values at the start of Column B. Could I have an idea of how might the macro looks like?
View 9 Replies
ADVERTISEMENT
Jun 17, 2008
I am having trouble with the Find method in Excel VBA.
This is the piece of code that I have written:
Sub NCMVariables()
Dim x As Integer
Dim c As Object
Sheets("Data").Select
With Worksheets("Data"). Range("A1:A60000")
Set c = .Find("USD", LookIn:=xlValues)
If Not c Is Nothing Then
View 3 Replies
View Related
Apr 16, 2008
I'm writing some code where I need to know the column in which a specifc text string occurs. Because of the nature of the sheet, the string will only appear in one column, but that could be in almost any column on the worksheet.
For example say the text string is "Year 1", I need to find the column with this string, and then offset down to a predetermined row, or offset over to the appropriate column (something like below).
With Selection
.Offset(0, x).Select
If List.Selected(0) = True Then
y = i - 1
With Selection
.Value = Salary * Inflation1 ^ y
End With
Else
View 9 Replies
View Related
Mar 1, 2008
I have the following macro which I've pieced together which works great at clearing the cell as long as the text entered by the user is found in the specified column.
Unfortunately I'm a big rookie with this stuff and I can't figure out how to handle the process/error when an entered value is not found. When ChosenRow returns with a 0 I get a Run time error '91', Object Variable or With Block variable not set.
The code...
Private Sub CommandButton1_Click()
oldSheet$ = ActiveSheet.Name
Dim ChosenRow As Long
Dim loc As Variant
Application. ScreenUpdating = False
With UserForm4
loc = .TextBox1.Text
End With
With ActiveWorkbook
Sheets("Reference").Select
End With
View 5 Replies
View Related
Oct 1, 2008
I have a file that the user selects and when they enter a value (in this case, a job title) into the input box, my macro looks for the value in that file. If the value is there, a msgbox pops up that lets the user know that the value was found and it then goes to that cell, displaying in the next msgbox the cell address of where the value was found. I already have the code down for this part of the macro.
My problem is what happens if the file has the same value more than once. Ideally, I'd like to display a message box that returns the addresses of both cells with the same value and then prompts the user to select one of these values as the value they are looking for. I am not sure if a msgbox or a msgbox and then an input box are most appropriate for this situation. Once the user does this, the macro continues. The rest of my macro is built on the cell where this value is, so it is crucial that I make sure there is at least one value selected. Any help is much appreciated. I have included a sample worksheet of what this situation might look like.
Here is the code I presently have for this part of my macro.
Sub GetOpenFileName()
strFind = InputBox("Please enter the job title you wish to search for:", "Search for job title in this file")
If strFind = vbNullString Then Exit Sub
With Sheets(strSheetsMainCompProfile)
If WorksheetFunction. CountIf(Range(Cells(1, 1), Cells(100, 100)), "*" & strFind & "*") = 0 Then
MsgBox strFind & " cannot be found on this sheet"
Else
View 9 Replies
View Related
May 10, 2008
I am trying to clean up and simplify my macros by compartmentalizing frequently repeated commands. I like to search for text in column headings and define the cell containing it as a range that I can later use .column or .row commands on, so my macros are a bit more flexible. The code I currently use for this is
Dim XColumn As Range ‘the cell containing the text “X”
Cells.Select
Selection.Find(What:="X", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Set XColumn = ActiveCell
I would like to be able to create a Macro (possibly name FindSetCell()) that will accept arguments and achieve the results of the above code in other macros
I think the command might look something like FindSetCell(“text to be searched for”,”name of newly Defined Range”) But I have not figured out how to code it
View 2 Replies
View Related
Oct 10, 2006
I'm looking for a formula, which says if a number is within a range, perform a calculation. I have a table of ranges (0-6000,6001-10000,10000-12000, and so on). My formula looks at the current month cumulative balance, and inserts the value into the line of the range it falls into. However, the formula now contains lots of nested "IF" statements, due to the number of ranges I have. Also, if my cummulative balance is 6005 for example, but was 5005 in the previous month, I would like to see 95 in the 0-6000 row, and 5 in the 6000-10000 row. I making a complete nonsense of trying to explain myself, so I'll attach a quick example of what I'd like to see.
View 4 Replies
View Related
Sep 27, 2007
I want to set range a entire column,the rows are dynamic and i want to do it on all the columns in the sheet, my problem is that after i set the range(i hope i did good) i want to do a average of the range and to put the result in a new sheet on a row. I'm sorry for the mess in the code its one of my first macros
Option Explicit
Sub Average()
Dim R As Integer
Dim help As String
Dim Range1 As Range
Dim E As Integer
Dim Avg As Integer
R = 1
'/////CREATE NEW SHEET AND COPY THE FIRST TWO ROWS TO THE NEW ONE
Sheets("UXP data").Select
'Sheets.Add
Sheets("UXP data").Select......................
View 2 Replies
View Related
Jun 9, 2009
I have one master worksheet with 1000 ID's. I also have 10 other spreadsheets with 1000 ID's. I need my macro to use the master worksheet and identify where the IDs are located among the 10 spreadsheets. If there is a match, then my macro should copy the entire row and place it in an output file (another spreadsheet).
The following code only tests the master worksheet and three other sheets (one being null/error is ID not found).
Sub Macro5()
'
' Macro5 Macro
Dim Raw, Mix1, Mix2, error As Excel.Worksheet
Set Raw = Sheets("RAW_DATA")
Set Mix1 = Sheets("Mix1")
Set Mix2 = Sheets("Mix2")
Set error = Sheets("error")
Raw.Select
For x = 1 To 1000
For y = 1 To 1000
If Raw.Cells(x, 1) = Mix1.Cells(y, 1) Then.................
View 9 Replies
View Related
Aug 3, 2009
I found a macro that searches a column on each worksheet and copies the searched for value to another worksheet but. I would like to modify this to copy the searched for value and the next 10 cells in the row but I am not able to figure out how to accomplish this. I highlighted in red the piece I would like to modify.
For Each sh In ActiveWorkbook.Worksheets
With sh.range("c10:c10000") 'this determines where to search
For I = LBound(MyArr) To UBound(MyArr)
'If you use LookIn:=xlValues it will also work with a
'formula cell that evaluates to "@"
'Note : I use xlPart in this example and not xlWhole
Set Rng = .Find(What:=MyArr(I), _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
FirstAddress = Rng.Address
Do
Rng.Copy NewSh.range("d1000000").End(xlUp).Offset(1,0)
Set Rng = .FindNext(Rng)
Loop While Not Rng Is Nothing And Rng.Address FirstAddress
End If
Next I
End With
Next sh
View 9 Replies
View Related
Nov 28, 2012
I have been trying to write VBA which basicly search for specific value in column A and copy values from that cell to down and two columns next. Select row and columns down after specific value in column A was find.
Like this
A B C
... ... ...
X 100 115
... ... ...
up to last row
I try with VBA below but it do not works.
Sub Select_Rows_GK()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i).Value = "9000" Then
Range("A" & Rows.Count).Offset(0, 1).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
Exit For
End If
Next i
End Sub
View 3 Replies
View Related
Jan 30, 2014
I'm trying to create a macro that can lookup values down a list, find that value within another list and copy the adjacent cells.
The values to look for will always be on the same column and the values to look into will always be in the same range or columns but not always in the same row.
For example.
AI:AI contains a list of ID's which will be manually input every day so they might have a different order.
C:AF has all the data to look into. C always being a list of ID values and I want for every ID found on AI:AI to be search for on C:C: and then once a match is found, I need specific adjancent cell values to be copied next to the ID found on AI.
The values that need to be returned are found on columns B,D,F,G,H,I,J,P,AF and they will always display on those columns.
I've been doing this based on vlookup and INDEX/MATCh formulas but I've come across some limitations for something I want to develop further so I'm trying to do this based on VBA.
View 5 Replies
View Related
Feb 27, 2012
The formula has to include a range across each row (for each student). The formula has to search for specific text contained in the classnames, and then return the result along the same row.
Below is an example, which I hope saves OK in the thread. If not I'll upload an Excel file:
A B C D Results:-
1 class1 class2 class3 class4 Gg Hi Fr Sp
2 10m/Gg1 10m/Hi2 10m/Fr1 10m/En1 TRUE TRUE TRUE FALSE
3 10n/Hi1 10n/En2 10n/Sp1 10n/Ma1 FALSE TRUE FALSE TRUE
View 14 Replies
View Related
Aug 23, 2013
I am trying to count the number of specific words contained in a specific cell. If my data were static, and the list of keywords was short, there are many solutions such as using multiple instances of the length function as proposed by @shg here.
Suppose I have the following sheet: (column C is what I am trying to achieve)
A
B
C
1
[Code]....
That is, C2 will tell that B2 contained 3 of the keywords (1X beautiful, 2X happy) and so on.
View 9 Replies
View Related
Apr 5, 2008
I have found many posts similar to what I am trying to accomplish, but nothing that I have been able to modify and make work. I need a VBA script that will find a cell with the text data "Difference" and copy the adjacent (to the right) cell's data. I then need to find a cell with text data "Ops" and paste the previous data to it's adjacent cell.
View 3 Replies
View Related
Jul 17, 2014
What I want to do is Average out a range of cells "F4:F343". That's the easy part =average(F4:F343). However I want to exclude any numbers for that average if data in coloumn "Q" = "FMC"
Would that look anything like:
=average(F4:F343)/countif(Q4:Q343, "<>FMC")
I'm also displaying it as minutes in a day so i added *24*60 to the end of the formula and formatted to Number/.00. I've entered the formula and it will calculate but when i enter FMC to the appropriate area the average wont change.
View 2 Replies
View Related
Aug 6, 2013
I need method, using a button, that looks at a cell--say EO2, for example--, looks back on a master worksheet at a specified row and range for a match, then looks at the information from a specified range below the matching cell (The information in this column will either be blank or have an "X" in the cell), and then those rows that do not have an "X" will be hidden in the corresponding rows in the working worksheet. Therefore, if at any time the value in "EO2" ever changes, then it will automatically find a new match and repopulate and hide information as before. About 130 columns will have its own button so that a "query" can be made that depends on the information in a particular cell in that column.
The master worksheet now has matrix of 287 rows and 58 columns. Each row is for an operating procedure and each column shows a job code. An "X" in a coordinate cell for a column/row shows whether that job code is responsible for knowing that operating procedure. So, on the working sheet, an employee's primary job code is given underneath his or her name. When the button is pushed, all the operating procedures not required for a given person will be hidden and only the required ones will remain visible--grouped, if you will. Qualification dates will be easier to see now that the information is consolidated. Whenever someone transfers to a new position, a new code will be inputed on the working sheet. When the button is pushed, a new grouping will result. Any operating instructions that overlap will still have qualification dates, so that information will not need to be transcribed.
View 9 Replies
View Related
Feb 2, 2010
I have a base document that i can import another data document with a button (this is working).
I then have another WS ("search") in the base document with lots of identifiers which I want to use as my search range to look through the document that I just imported (column A).
I need a msg to ask the user what month they would like to find the $ value on (Ie, January) in the imported WS... this way it doesnt copy the entire line only cell in the selected month column.
Then I want to the user to be able to click a button that will check through the identifiers on the "search" WS and if the same identifier appears in the imported WS in column A, then for the $ value in the column selected to be copied to the search Range work sheet.
If the idenfifier is not matched then in place of the $ value copied can be the string "no in XXX WS" .
I have attached the document with dummy data in each work sheet with details more cleary what I have meantion above.
View 9 Replies
View Related
Aug 27, 2006
I'm trying to create an Excel function that allows me to choose a row from a table based on text found within entered data. For example, say I have a table called "categories" that looks like this:
Categories
cat
dog
mouse
Now in a different section of the workbook, I want to be able to enter an arbitrary number of sentences, one per row, that look something like:
"It's raining cats."
"The mouse is hiding."....................
View 3 Replies
View Related
Mar 16, 2013
Trying to assign code to comm. button on User form to copy lets say:
(sheet1, rangeB2:B21) to (sheet2, first blank row rangeB2:B21) and paste it as text value one more question: what to be aware in case of sheets format (merged cells, hidden rows...)
View 4 Replies
View Related
Mar 17, 2008
Dim ColNo As Byte
Dim LastCol As Byte
ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range("sheet1!b1").End(xlToRight).Column
But the below code errors?
Dim ColNo As Byte
Dim LastCol As Byte
ColNo = Application.WorksheetFunction.Match("Column find", Range("sheet1!A1:Z1"), 0)
LastCol = Range(Cells(1, ColNo)).End(xlToRight).Column
View 3 Replies
View Related
Feb 21, 2007
I want create a macro which will find a text "9:55:00" in the cell of column A and if found copy that entire row in a new workbook which i have opened. the text "9:55:00" will be there every workbook i will open. Once the row is copied in the new workbook, when i again do the second find the row must be copied below the first find in the new workbook.
View 4 Replies
View Related
Jan 5, 2007
Another question where i think my logic is ok i'm just not sure on the syntax.
I'm trying to find a range of cells by a criteria and the copy that range to another part of the sheet, The finding the data is ok i think, its just the copying... SpecialCells
Private Sub GetMainClass()
Dim Class As Range
Dim Cell As Range
Dim Destination As Range
Dim Counter As Integer
'searches entire sheet
Set Class = Selection.SpecialCells(xlConstants, xlTextValues)
' Initial setting of the counter. first row for data is the integer
Counter = 20
'set up the destination range with a counter to increment the row so data doesn't overwrite
Set Destination = Range(Cells(B, Counter), Cells(I, Counter))
For Each Cell In Class
If Cell.Value = "Main" Then
Cell.EntireRow.Copy Destination
Counter = Counter + 1
End If
Next Cell
Application.CutCopyMode = False
End Sub
View 5 Replies
View Related
Jun 23, 2014
I need to find a specific text in column D of sheet2 of my worksheet and copy that row to sheet1 to the last blank cell in column B. The macro should continue to search for the next text and copy rows only if there is active cells in the range column A : C of sheet2. I need a VBA code to do this.
View 5 Replies
View Related
Jul 11, 2014
See attached spreadsheet for reference : MacroHelp.xlsx‎
I would like the macro to look in column I, and when it finds "TRUE", I'd like it to copy and paste values in all the cells to the left of it.
Reason being is that I want to rid the row of its formula, so the next time someone changes the info (the date the vlookup is searching for) in the tabs those cells are referring to, I will not lose the data it returned previously.
Really all I want is to copy and paste the values in the lowermost row of new information for each plant, but this is the best way I can currently think of.
I've removed the tabs the cells in columns B-G are referring to for sensitivity's sake.
View 2 Replies
View Related
Feb 15, 2014
Finding any specified Text like "Cube" down a specified Column in this case "D" when Text has been found
Copy the 24 cells directly below and Then Paste to G1.
View 7 Replies
View Related
Jul 12, 2007
I tried to writer my code myself but I have a long way to go. Here is what I must do for my case:
I want a macro to find the row that include a specific text (For example "SMSC") in a range (A1:A100) then copy this entire row below the cell which has the value "OTHERS". That is, if there are 10 pieces of "SMSC" so these rows including "SMSC" should be listed below the cell "OTHERS"
View 9 Replies
View Related
Feb 6, 2014
I have the following;
Code:
=IF(COUNTIF(AG:AM,K12),"Yes","No")
How can I get that to look at the range and when it finds similar items that it gives a Yes. For instance Cell K12 may have "Tractor" and it may have "Tractor Trailer" so I need it to say Yes if it sees any combination using Tractor (or the contents in column K). I tried to use left(K12,4) but that did not seem to work.
View 2 Replies
View Related
Dec 29, 2006
I want to search a text in a range...and not in a particular cell...
For example,
Col A-------------------------------Col B
2306-------------------------------JAHSN-AJS-2398-2US9
2332-------------------------------AHSNAHSB 2346 09
AND SO ONNNNNN
Suppose I want to search if 2306 is available in col B by any chance?
View 3 Replies
View Related
Feb 25, 2014
I'm using a macro for searching through ~200k rows of a column, finding all the occurrences of a string and copying them to another column.
This is the code (copied from somewhere some time ago, modified as needed) :
[Code] .....
It works great but it takes a little less than 20 seconds to complete the task.
And, since I have to search for multiple strings and the results need to be copied to different rows, I use multiple subs like this in a bigger macro.
The problem is that it got to the point where it takes 3 minutes to execute that bigger macro and I'm trying to find a way to speed things up.
View 5 Replies
View Related
