Userform Match - unable to get the match property of the worksheetfunction class - Help!

Hi guys,

I have a userform, that when initializes checks to see if the current computer name of the user is currently active, but keep getting the error "Unable to get Match property of the worksheetfunction class"

Any help would be much appreciated.

Public CurRng As object

Private Declare Function GetComputerName Lib "kernel32" _
Alias "GetComputerNameA" (ByVal lpBuffer As String, nSize As Long) As Long
 
Function ComputerName() As String
    Dim rString As String * 255, sLen As Long, tString As String
    tString = ""
    On Error Resume Next
    sLen = GetComputerName(rString, 255)
    sLen = InStr(1, rString, Chr(0))
    If sLen > 0 Then
        tString = Left(rString, sLen - 1)
    Else
        tString = rString
    End If
    On Error GoTo 0
    ComputerName = UCase(Trim(tString))
End Function

Private Sub UserForm_Initialize()

Set CurRng = Sheets("System_Info").Range("A34:B62")

Dim l As Long
l = Application.WorksheetFunction.Match(ComputerName, CurRng.Columns(1), 0)
If l <> 0 Then
MsgBox "Found " & ComputerName & " at row : " & l
Else
MsgBox "No Match Found"
End If
End sub

Try...

Private Sub UserForm_Initialize()

    Dim MatchVal As Variant
    
    Set CurRng = Sheets("System_Info").Range("A34:B62")
    
    MatchVal = Application.Match(ComputerName, CurRng.Columns(1), 0)
    
    If IsError(MatchVal) Then
        MsgBox "No Match Found"
    Else
        MsgBox "Found " & ComputerName & " at row : " & MatchVal
    End If

End Sub

Note that the row number returned is relative to the defined range.

brilliant, works a treat, thank you

You're very welcome! Thanks for the feedback!