vbaexcelforeachtext-comparison

Excel Looping through cells with vbTextCompare to assign categories

I have a spreadsheet which contains a summary Column(Column K) on each row. I need to match certain words in the summary column in order to assign a category name in a new column(Column V).

I tried doing this with a normal excel If statement but I have sinc efound out there is a limit. So now I'm trying to use the following VBA code.

Public Function getCategory()

V_End_Of_Table = ActiveSheet.UsedRange.Rows.Count 'count the number of rows used'

Dim cell As Range


For Each cell In Range("K2:K" & V_End_Of_Table) 'loop through each row until end of table'


 If InStr(1, cell.Value, "Nationalities", vbTextCompare) > 0 Then
    Range("V" & V_End_Of_Table).Value = "Nationalities"
Else
    Range("V" & V_End_Of_Table).Value = "No Match Found"
End If

Next 'move onto next cell'

End Function

So I'm trying to loop through each row, match the text and assign the value. As it stands at the moment I just get #VALUE! returned. If I change

Range("V" & V_End_Of_Table).Value

to

MsgBox

it will return the correct string.

Solution

  • like this bro:

    For Each cell In Range("K2:K" & V_End_Of_Table)
    If InStr(1, cell.Value, "Nationalities", vbTextCompare) > 0 Then
        Range("V" & cell.Row).Value = "Nationalities"
    Else
        Range("V" & cell.Row).Value = "No Match Found"
    End If
Next

    instead of InStr you could use StrComp function to compare 2 words