CODEWITHSUNDEEP
stringexcelexcel-2007rowvba
user1487002
user1487002

Reputation: 1

Excel 2007 Using the 'Find' function for a user-defined string, then copying the entire row for each occurrence to another sheet

I'm new to Excel VBA, and this is my first attempt to make a useful macro to speed up my work process.

What I'm trying to do

I have a data file of abut 15 thousand rows by about 15 columns. What I would like my macro to do is once I hit a button on a separate sheet, the code takes a string I have typed into a specific cell on that sheet, goes to the sheet with all of the data on, then uses the find function on one of the columns to find all instances of the string which I have defined.

Once all of the instances of the string have been located, I want to copy the corresponding rows and paste them into the sheet I ran the macro from.

To clarify, the column I want to locate the string in contains descriptions typed by people - there isn't just one word to look at; that is why I have been trying to use the Find function.

My attempt so far:

Sub FindTextBasicData()

'Define variables
Dim sourceSht As Worksheet
Dim outputSht As Worksheet
Dim strSearch As String
Dim searchRange As Range
Dim outputRange As Range

'Set the sheet variables to those present in the workbook
Set sourceSht = Sheets("Basic Data")
Set outputSht = Sheets("Output")

'Set the value of the string variable to the contents of cell C2 in the output sheet
strSearch = outputSht.Range("C2")

'Set the range variable to the range in the data sheet that I want to check    
Set searchRange = sourceSht.Range("C6:C15448")

'Use the Find function to look through the range I defined and select all rows where the
'string variable can be found, setting the second range variable to these values
Set outputRange =searchRange.Find(What:=strSearch, After:=.Cells(3, 6), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False).EntireRow

'Copy the results of the Find function to the clipboard
outputRange.Copy

'Select the 5th row of the output sheet as the location to paste the data, and then paste   
outputSht.Select
Rows(5).Select
ActiveSheet.Paste

End Sub

I know that I'm definitely doing something wrong with the find function, but I can't figure the thing out - I think my problem lies with the After parameter in that it doesn't do what I think it does (references cell C6 as the place to start using Find from?). I tried looking at the guide to the Find function on Ozgrid, but I think I just confused myself more.

If I can get this macro to work correctly I will be able to use it a lot to vastly streamline the analysis of data that I have to do for these 15000 records. Any help for this would be greatly appreciated, and I'm of course happy to clarify if I haven't explained something well enough.

Upvotes: 0

Views: 2421

Answers (3)

JimmyPena
JimmyPena

Reputation: 8764

Instead of the Range.Find method, use the Range.AutoFilter method to filter the rows on the first sheet based on the value on the second sheet, then copy only the visible rows. IMHO, this is better than the Range.Find method (which is still better than looping).

There are numerous examples on this site about how to copy visible rows to another sheet, here is one example: Excel Macros - Copy and paste filtered rows

Upvotes: 0

ps-aux
ps-aux

Reputation: 12174

As mentioned above. You use dot operator in front of .Cells(3, 6) without With. The best way is to reference it to concrete sheet directly sourceSht in your case. If you want to reference to cell C6 then you can use for example :

sourceSht.Range("C6") or sourceSht.Cells(6,3) or sourceSht.Cells(3,"C") etc..

But I think that shouldnt cause problem (provided the reference is valid) because After parameter is not relevant (and optional) if all you want to do is search in the whole range. In fact only What is required parameter.

set outputRange = searchRange.Find(strSearch).EntireRow should do the trick. Moreover if you specify After parameter, the search doesnt look into that cell.

Anyway, this only gives you the first cell in the row in which the string was found. Not all of them. You might want to put the search code then into a cycle in combination with FindNext method or just using the After parameter of Find method.

Upvotes: 0

barrowc
barrowc

Reputation: 10679

The reference .Cells(3, 6) needs to be qualified using a With block or just directly refer to a Worksheet or Range object. Easiest solution here would be sourceSht.Cells...

Also, Cells(3, 6) is cell F3 whereas you want cell C6. Put these together and you should therefore have After:=sourceSht.Cells(6, 3)

Upvotes: 1

Related Questions