vba - Excel Looping through cells with vbTextCompare to assign categories -

i have spreadsheet contains summary column(column k) on each row. need match words in summary column in order assign category name in new column(column v).

i tried doing normal excel if statement have sinc efound out there limit. i'm trying use following vba code.

public function getcategory()  v_end_of_table = activesheet.usedrange.rows.count 'count number of rows used'  dim cell range   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     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 loop through each row, match text , assign value. stands @ moment #value! returned. if change

range("v" & v_end_of_table).value

to

msgbox

it return correct string.

like bro:

for each cell in range("k2:k" & v_end_of_table)     if instr(1, cell.value, "nationalities", vbtextcompare) > 0         range("v" & cell.row).value = "nationalities"     else         range("v" & cell.row).value = "no match found"     end if next 

instead of instr use strcomp function compare 2 words