title: Excel Looping through cells with vbTextCompare to assign categories
body: <p>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).</p>
  
  <p>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.</p>
  
  <pre><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" &amp; V_End_Of_Table) 'loop through each row until end of table'
  
  
   If InStr(1, cell.Value, "Nationalities", vbTextCompare) &gt; 0 Then
      Range("V" &amp; V_End_Of_Table).Value = "Nationalities"
  Else
      Range("V" &amp; V_End_Of_Table).Value = "No Match Found"
  End If
  
  Next 'move onto next cell'
  
  End Function
  </code></pre>
  
  <p>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 </p>
  
  <p>Range("V" &amp; V_End_Of_Table).Value </p>
  
  <p>to </p>
  
  <p>MsgBox </p>
  
  <p>it will return the correct string.</p>