How can I use match in excel vba?

rgomeceria

New Member
Joined
May 4, 2021
Messages
1
Office Version
  1. 2019
Platform
  1. Windows
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...
VBA Code:
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
 
Last edited by a moderator:

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Snakehips

Well-known Member
Joined
May 17, 2009
Messages
5,358
Office Version
  1. 2013
Platform
  1. Windows
@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

Well-known Member
Joined
Apr 5, 2021
Messages
1,240
Office Version
  1. 2010
Platform
  1. Windows
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 …​
 

Forum statistics

Threads
1,141,398
Messages
5,706,207
Members
421,433
Latest member
yash0468

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top