Quick help needed with some excel VBA code!

[joxang]

The code below was copied off a VBA learning site - it's a macro for copying and pasting particular rows into a new sheet. The macro selects any rows that contain a particular value in a particular column, and copies all of the selected rows into the next sheet.

Spoiler

Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer

On Error GoTo Err_Execute

//Start search in row 4
LSearchRow = 4

//Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

//If value in column H = "searchterm", copy entire row to Sheet2
If Range("H" & CStr(LSearchRow)).Value = "searchterm" Then

//Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

//Paste row into Sheet2 in next row
Sheets("Sheet2").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

//Move counter to next row
LCopyToRow = LCopyToRow + 1

//Go back to Sheet1 to continue searching
Sheets("Sheet1").Select

End If

LSearchRow = LSearchRow + 1

Wend

//Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Exit Sub

Err_Execute:
MsgBox "An error occurred."

End Sub

I wanted to edit this so instead of searching for the EXACT cell value (Value="searchterm"), it would search for a particular string inside the cell. For example, at the moment, the code below only returns positive for cells containing only "searchterm" and not for a cell containing "blah blah searchterm blah".

Is there an easy way of doing this? I've been trawling vba forums for hours but can't find anything useful or anything I can understand. There's about 7000 entries in this excel database so a macro is pretty essential, and I can only work on computers using excel 2003. Would be extremely grateful for any help, thanks in advance .

 

[Pho]

Untested, but try replacing:

If Range("H" & CStr(LSearchRow)).Value = "searchterm" Then

with:

If InStr(Range("H" & CStr(LSearchRow)).Value, "searchitem") > 0 Then

 

[mattx2]

Instead of = use like and add a * on the start and end of the searchterm

 

[joxang]

Cheers guys, I'll test it tomorrow when I'm back at work and post back .

 

[joxang]

Untested, but try replacing:

If Range("H" & CStr(LSearchRow)).Value = "searchterm" Then

with:

If InStr(Range("H" & CStr(LSearchRow)).Value, "searchitem") > 0 Then

This worked a charm...many thanks !!!