title: Application.worksheetFunction.match not working correctly VBA Excel
body: <p>I am trying to generate a unique ID with a prefix. to so this I am generating a random number between 0000 and 9999 and adding that to the end of my prefix. What I want to then do is check the sheet in column A to see if that ID already exists and if so then it just generates another random number and it carries on till a unique one is found. To do this I am using a while loop. Below is my code. The issue that I am having is that once the match() functions does not find anything it the value remains as the last value ie: the row number that it found the last value. For example, if my id is T26 - 7055 it searches the sheet and finds that it already exists in row 59. it then generates another ID this time say T26 -2099 which does not exist, but the match function still returns 59 and the while loops just goes into infinite loop. I dont understand why this is happening and was hoping one of you guys would be able to help.</p>
  
  <pre><code>    Dim newID As String
  Dim x As Integer
  Dim matchedRow As Boolean
  
  matchedRow = True
  x = CInt(Int(9999 * Rnd()) + 1)
  newID = "T26 - " &amp; x
  While matchedRow = True
      Dim match As Long
      On Error Resume Next
          match = Application.WorksheetFunction.match(newID, Sheets("Exceptions").Columns(1), 0)
     On Error GoTo 0
      If match &lt;&gt; 0 Then
          x = CInt(Int(9999 * Rnd()) + 1)
          newID = "T26 - " &amp; x
          matchedRow = True
  
      Else
          matchedRow = False
      End If
  Wend
  </code></pre>
  
  <p>Thanks</p>