vba compare script help

[jonathan_smithey]

Hi guys,

I've written a bit of VBA script within Excel that searches through data within one column of an excel spreadsheet and where a specific word is named in that column (this variable is data that is entered in a specific cell), copy several cells from that row to a different worksheet. This works well but it has a limitation - it only searches for words that exactly match the case of the word(s) entered. i.e if the word "The" was entered as the search criteria only rows containing the word "The" would be returned. Rows with the word "THE" or "the" would not be returned. Other than converting everything to upper case prior to the search, is there any way that the code can be made to seach "case insensitive"?

The code that I'm using is:
(the variable "SearchWord" is the criteria that's entered in a cell on a worksheet)

'this will only search upto row 1000
For sRow = 1 To Range("E1000").End(xlUp).Row
If Cells(sRow, SearchColumn) Like "*" & SearchWord & "*" Then
sCount = sCount + 1
dRow = dRow + 1
Cells(sRow, "A").Copy Destination:=DestSheet.Cells(dRow, "A")
Cells(sRow, "B").Copy Destination:=DestSheet.Cells(dRow, "B")
Cells(sRow, "C").Copy Destination:=DestSheet.Cells(dRow, "C")
Cells(sRow, "D").Copy Destination:=DestSheet.Cells(dRow, "D")
Cells(sRow, "E").Copy Destination:=DestSheet.Cells(dRow, "E")
End If
Next sRow

Any help/assistance would be greatly appreciated

 

[VoG]

Hello and welcome to MrExcel.

Try putting this at the top of the module, before any Subs.

Option Compare Text

 

[jonathan_smithey]

Magic - it works...

Thanks for the super, speedy response.

Is it easy for you to explain what that command does so that I can better understand for next time?

 

[VoG]

Well I could explain but why not click in the word Like in your code and press F1 to get Microsoft's explanation