CODEWITHSUNDEEP
vbafunctionexcelmatch
GBSingh
GBSingh

Reputation: 426

Application.worksheetFunction.match not working correctly VBA Excel

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.

    Dim newID As String
Dim x As Integer
Dim matchedRow As Boolean

matchedRow = True
x = CInt(Int(9999 * Rnd()) + 1)
newID = "T26 - " & 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 <> 0 Then
        x = CInt(Int(9999 * Rnd()) + 1)
        newID = "T26 - " & x
        matchedRow = True

    Else
        matchedRow = False
    End If
Wend

Thanks

Upvotes: 1

Views: 9329

Answers (3)

John Coleman
John Coleman

Reputation: 51998

You are not resetting match to 0 after you find a match.

After the line

matchedRow = True

put the line

match = 0

Having Dim match as Long at the start of the loop doesn't reset it.

Upvotes: 2

paul bica
paul bica

Reputation: 10715

  • Try renaming variable "match" to something else - Match is the name of the built-in Excel function

  • Use Application.Match instead of WorksheetFunction.Match (there are subtle differences)

Dim newID As String
Dim found As Variant

Do

    newID = "T26 - " & CInt(Int(9999 * Rnd()) + 1)

    found = Application.Match(newID, Sheets("Exceptions").Columns(1), 0)

Loop While Not IsError(found)

Upvotes: 2

Steve
Steve

Reputation: 397

You are using Application.WorksheetFunction.match. This will throw an error if the match is not found and you have found a way around it. But this will also supress any legitimate errors that are found.

If you use Application.match you will we able to trap the error as follows:

Application.WorksheetFunction.IsError(Application.match(newID, Sheets("Exceptions").Columns(1), 0))

This will at least give you an idea of the error you are encountering. Also: you are not using the exact match for your matching. I assume that, based on your story you will need that (this may be the cause for your troubles. Just change the last 0 to a 1. You will get a more exact result.

Application.WorksheetFunction.IsError(Application.match(newID, Sheets("Exceptions").Columns(1), 1))

Upvotes: 1

Related Questions