VBA like operator in range

Question

I have the following code and I am trying to check the cells in the range for a specific word. The word in this case is Test. It can come up in all sorts of ways, ex: Test program/ Program Test/programTest etc.

How do I make the LIKE operator work in my case? I can't seem to make it work in this case *Test or with any other operators.

What am I missing?

Sub Macro1()

Dim lastrow As LongPtr
Dim TagSheet As Worksheet
Dim LDRange As Range

Set TagSheet = Worksheets("sheet1")

TagSheet.Activate

lastrow = TagSheet.Range("AO" & Rows.Count).End(xlUp).row

Sheets("sheet1").Activate
ActiveSheet.Cells(lastrow, 41).Select

Range(Selection, Selection.End(xlUp)).Select


Set LDRange = Selection


LDRange.Select


For Each Cell In LDRange

  
  If Cell Like "*test" Then
    MsgBox "yes"
  Else
    MsgBox "no"
  
  
  End If
Next

Side note: you want to [avoid the use of Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) in your code — cybernetic.nomad, Apr 11 '22 at 17:06

score 4 · Answer 1 · answered Apr 11 '22 at 17:05

4

Something like this should work:

Sub Macro1()

    Dim c as Range, TagSheet As Worksheet

    Set TagSheet = Worksheets("sheet1")

    For Each c in TagSheet.Range("AO1:AO" & TagSheet.Cells(Rows.Count, "AO").End(xlup).Row).Cells 
            'force to lower case to avoid case-sensitivity
            Debug.Print c.Address, IIf(lcase(c.Value) Like "*test*", "Yes", "No")
    Next c
End Sub

answered Apr 11 '22 at 17:05

Tim Williams

154,628
8
97
125

yep worked with Lcase. thanks – Edmond Manta Apr 13 '22 at 07:44

JvdV · Answer 2 · 2022-04-11T17:08:50.277

The Like() operator is very much so case-sensitive;
You do not currently have a wildcard at the end (unsure if meant like that).

Therefor the results from:

Sub Test()

Dim arr As Variant: arr = Array("Test", "test", "Text test text", "Text Test text")

For Each el In arr
    If el Like "*test*" Then Debug.Print el & " - Case sensitive
Next

End Sub

Are going to be very different too:

For Each el In arr
    If el Like "*[Tt]est*" Then Debug.Print el & " - Case insensitive
Next

Here we just simply added an upper- & lowercase 't' inside the character class.

Note: Another option would be to use Option Compare Text right before the module. It would force case-insensitive text comparison.

VBA like operator in range

2 Answers2