How can I use match in excel vba?

[rgomeceria]

I am just new in excel vba, can you help me with this? I wrote this program, when it hits the function match, it gives me run time error # 13, also is there any wrong with the match function I wrote? Thanks for the help...

Private Sub CommandButton1_Click()
    On Error GoTo eh
    Dim user As String
    Dim password As String
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ThisWorkbook
    Set ws = wb.Worksheets("System User Name")
   
    user = Me.TextBox2.Text
    password = Me.TextBox3.Text
   
    If Application.WorksheetFunction.Index(ws.Range("c2:C5"), Application.WorksheetFunction.Match(user, ws.Range("c2:c5"), 0)) = True And Application.WorksheetFunction.Index(ws.Range("b2:d5"), Application.WorksheetFunction.Match(ws.Range("b2:d5"), 0)) = True Then
        Sheets("bis").Activate
        Range("b124").Value = FullName
        Sheets("r&t ma-ca&ck").Activate
        Range("b95").Value = FullName
        Sheets("pdc ma-ca&ck").Activate
        Range("a40").Value = FullName
        Sheets("r&t ma-otpp").Activate
        Range("b63").Value = FullName
        Sheets("pdc ma-otpp").Activate
        Range("a40").Value = FullName
        Sheets("pdc blank template").Activate
        Range("a40").Value = FullName
        Sheets("bsop").Activate
        Range("b145").Value = FullName
        Sheets("petty cash fund").Activate
        Range("a37").Value = FullName
        Sheets("exchange cash fund").Activate
        Range("a37").Value = FullName
        MsgBox "You have successfully log in."
        Unload Me
        Application.Visible = True
    Else
        If LoginInstance < 3 Then
            MsgBox "Invalid login credentials. Please try again.", vbOKOnly + vbCritical, "Invalid Login Credentials"
            LoginInstance = LoginInstance + 1
        Else
            MsgBox "You have exceeded the maximum number of login attempts.", vbOKOnly + vbCritical, "Invalid Credentials"
            Unload Me
            ThisWorkbook.Close savechanges:=False
            Application.Visible = True
            LoginInstance = 0
        End If
    End If
   
eh:
    If Err.Number = 13 Then
        MsgBox "Invalid data, type is mismatch."
   
    End If
End Sub

 

[Snakehips]

@rgomeceria Welcome to MrExcel.

At first sight, it would appear that you do not have a Lookup Value parameter in the second Match expression ?

 

[Marc L]

​

First, you must use Application.Match as WorksheetFunction crashes the VBA procedure when nothin' is matching !​

​

You can check its result via VBA function IsError or IsNumeric …​