title: How to apply MATCH() function in VBA?
body: <p>I am trying to run the code below but get</p>
  
  <blockquote>
    <p>Error 1004: Unable to get the Match property of the WorksheetFunction class.</p>
  </blockquote>
  
  <p>I understand if there is no match, MATCH() function returns <em>#N/A</em>, so there is no point to assign this to the INDEX variable (moreover, I think it might also cause an error).</p>
  
  <p>How can I account for this possibility? </p>
  
  <pre><code>Sub Debugging()
  
  Workbooks("Problem.xls").Worksheets(1).Activate
  
  Cash_Rows = 5
  Share_Rows = 6
  
  If Cash_Rows &lt;= Share_Rows Then
  
      Range("A1:A" &amp; Cash_Rows).Select
      With Selection.Interior
          .ThemeColor = xlThemeColorAccent6
          .TintAndShade = 0.399975585192419 
      End With
  
      Count_Cash = Application.WorksheetFunction.CountIf(Range("A:A"), "L*")
  
      For Each cell In Range("A1:A" &amp; Cash_Rows)
          If CStr(cell.Value) Like "L*" Then
              Range("A" &amp; cell.Row &amp; ":" &amp; "D" &amp; cell.Row).Interior.Color = 65535
              Dim Index As Integer
              Index = Application.WorksheetFunction.Match(CStr(cell.Value), Range("F2:" &amp; "F" &amp; Share_Rows), 0)
              Range("F" &amp; Index &amp; ":" &amp; "I" &amp; Index).Interior.Color = 65535
          End If
      Next
  
      If Count_Cash = 0 Then
          MsgBox "You do not have any matching ID+Amount between Cash and Shares booking. It's OK!"
      Else
          MsgBox "You have " &amp; Count_Cash &amp; " matching transactions. Check them!"
      End If 
  
  Else 
  
      MsgBox "Do not worry. Be happy!" 
  
  End If 
  
  End Sub
  </code></pre>