Find syntax
 

Hi all

I have code that works fine to search through a worksheet row by row, locate
a string anywhere in a row and, if found, copy that entire row to a different
worksheet:

For r = firstRow To lastRow
Set c = Rows(r).Find("M123")
If Not c Is Nothing Then
Rows(r).Copy
Sheets("SubData").Activate
Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Range("A65000").End(xlUp).Offset(0, 0).PasteSpecial (xlPasteFormats)
Sheets("AllData").Activate
End If
Next r

However, I need to amend this so the search for a string is only carried out
on one particular column of each row (column K). I've tried all sorts of
permutations of the find syntax without success. Can anyone help please?

Many thanks
Sue

Find syntax
 

Hi Sue,

Try

Set c = Intersect(Rows(r), Columns("K")).Find("M123")



---
Regards,
Norman



"SueJB" wrote in message
...
Hi all

I have code that works fine to search through a worksheet row by row,
locate
a string anywhere in a row and, if found, copy that entire row to a
different
worksheet:

For r = firstRow To lastRow
Set c = Rows(r).Find("M123")
If Not c Is Nothing Then
Rows(r).Copy
Sheets("SubData").Activate
Range("A65000").End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Range("A65000").End(xlUp).Offset(0, 0).PasteSpecial
(xlPasteFormats)
Sheets("AllData").Activate
End If
Next r

However, I need to amend this so the search for a string is only carried
out
on one particular column of each row (column K). I've tried all sorts of
permutations of the find syntax without success. Can anyone help please?

Many thanks
Sue