An Excel Macro to find only the exact wordn & copy rows base

January 7, 2013 at 22:17:40
Specs: Windows Vista
An Excel Macro to find only the exact wordn & copy rows based on a keyword list

I am using the below script
Sub carHunter()

'Determine last row with data in Sheet 3 Column A

lastSrc_rw = Sheets(3).Range("A" & Rows.Count).End(xlUp).Row

'Loop through list of cars

For Each car In Sheets(3).Range("A1:A" & lastSrc_rw)

'Search for each car, copy row if found

With Sheets(1).Columns(1)

Set c = .Find(car, LookIn:=xlValues, lookat:=xlPart)

If Not c Is Nothing Then

firstAddress = c.Address


'Find next open row in Sheet 2

nxtDst_rw = Sheets(2).Range("A" & Rows.Count).End(xlUp).Row + 1

'Copy row containing found car

c.EntireRow.Copy Destination:=Sheets(2).Range("A" & nxtDst_rw)

'Search for same car again, stop when no more found

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress

End If

End With


End Sub

But if this script is searching for es then it wll copy all words whch has ed on it.
Please help.
want to copy only that row which has the exact value.

See More: An Excel Macro to find only the exact wordn & copy rows base

Report •

January 8, 2013 at 04:44:54
First a posting tip...

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum.

As for your question, the Lookat argument for the .Find method is set to xlPart which means that it will return any value where the search string is found, even if it is only part of the cell.

e.g. Searching for the text string "hat" will find "hat", "that", "top hat", "hate", etc.

If you only want to return values that are an exact match, change that argument to xlWhole. In that case the entire cell must match the search argument exactly.

Since I can't see your data from where I'm sitting, I am going to guess that the cells that contain "ed" also contain "es" somewhere in the cell.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •
Related Solutions

Ask Question