If IsError and WorksheetFunctions

[Tom59593]

Hey guys,

I am developing a function that does a couple of things. First, the user specifies player names in 'Scores'!A1:A1000, and then various statistics in 'Scores'!B1:G1000. The function loops through these entries, and then updates the corresponding cells in the 'Lifetime Statistics' sheet. In order to accomplish this, the function uses WorksheetFunction.Match to find the row containing the player name in the Lifetime Statistics sheet.

It works all fine and dandy, unless the user specifies a name that is not in the lifetime statistics sheet. For instance, if the user mis-spells a name or a new player joins the league, the function encounters an error at the line containing WorksheetFunction.Match.

So I thought I would include an If statement every time the function loops through another player to verify that their name can be found in the lifetime statistics sheet, but I am not having any luck. I assume it's because I can't use a WorksheetFunction inside of the IsError expression, but I don't know another way to check. Below is my code as it stands right now, any help would be greatly appreciated!

THANK YOU!!!

-Tom Winchester-

Sub AddScores()
'Define Worksheet Variables
Dim Lifetime As Worksheet
    Set Lifetime = Worksheets("Lifetime Statistics")
Dim Scores As Worksheet
    Set Scores = Worksheets("Scores")

'Define Range Variable for Names
Dim PlayerList As Range
    Set PlayerList = Lifetime.Range("C1:C1000")

'Define First Row Variables
Dim LifetimeFirstRow As Integer
    LifetimeFirstRow = 2
Dim ScoresFirstRow As Integer
    ScoresFirstRow = 2

'Define Last Row Variables
Dim LifetimeLastRow As Integer
    LifetimeLastRow = Lifetime.UsedRange.Rows.Count
Dim ScoresLastRow As Integer
    ScoresLastRow = Scores.UsedRange.Rows.Count

'Loop from first score row to last score row
For ScoresRow = ScoresFirstRow To ScoresLastRow
'Define score variables
    Dim Name As String
    Name = Scores.Range("A" & ScoresRow).Value
    '
    'Gather some other variables
    '
'Check if player is in database
    If IsError(WorksheetFunction.Match(Name, PlayerList, 0)) = False Then
    '
    'Perform the function operation
    '
    End If
Next ScoresRow
End Sub

 

[AlphaFrog]

Try something like this...

    x = 0
    On Error Resume Next
    x = WorksheetFunction.Match(Name, PlayerList, 0)
    On Error GoTo 0
    If x > 0 Then
    '
    'Match found at x
    '
    Else
    '
    'Match not found
    '
    End If

Or you could use the .Find method...

    Dim rFound As Range
    
    Set rFound = PlayerList.Find(What:=Name, LookIn:=xlValues, LookAt:=xlPart, _
                 SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
                 
    If Not rFound Is Nothing Then
    '
    'Match found at rFound.Address
    '
    Else
    '
    'No match found
    '
    End If