VBA to find a column number with match function

user_14460473 0
Viewed 373

I'm trying to find the column on a worksheet based on a value on another worksheet. The codes I am using only gets me value of 0 instead of 20 (it's column T on the worksheet). Can someone please tell me what's wrong with my codes and how to make it work? Much appreciated.

Sub Month1()

Dim ColNum As Integer

ColNum = WorksheetFunction.Match(Sheets("Std").Range("F2").Value, Sheets("SAN-68855").Row(3), 0)
vba excel
2 Answers

Here is the my testing and the output, perhaps your issue is different case?

Origninal data:

enter image description here

Sub colNum()

Dim rowNum As Long

rowNum = Application.WorksheetFunction.match("c", Sheet1.Rows(1), 0)
Debug.Print rowNum   'return 3

End Sub
user_158978610

Match in VBA

  • The main mistake is .Row(3) which has to be .Rows(3).
  • Note that if the value is not found, WorksheetFunction.Match will raise an error which you have to account for (2nd procedure). Therefore Application.Match is preferred (3rd and 4th procedure).
  • When using Application.Match the result will be either a whole number or an error value. Therefore you have to declare the variable as Variant so it can hold both data types.
Option Explicit

Sub Month1QF()
    ' A Quick Fix

    Dim ColNum As Long
    ColNum = WorksheetFunction.Match(Sheets("Std").Range("F2").Value, Sheets("SAN-68855").Rows(3), 0)
    MsgBox ColNum

End Sub

Sub Month1WM()
    ' WorksheetFunction.Match
    
    Dim ColNum As Long
    
    On Error Resume Next
    ColNum = WorksheetFunction.Match(Sheets("Std").Range("F2").Value, Sheets("SAN-68855").Rows(3), 0)
    On Error GoTo 0
    
    If ColNum = 0 Then
        MsgBox "The value could not be found.", vbCritical, "Get Column Number"
    Else
        MsgBox "The column number is '" & ColNum & "'.", vbInformation, "Get Column Number"
    End If
    
End Sub

Sub Month1AM()
    ' Application.Match: IsError
    
    Dim ColNum As Variant
    
    ColNum = Application.Match(Sheets("Std").Range("F2").Value, Sheets("SAN-68855").Rows(3), 0)
    If IsError(ColNum) Then
        MsgBox "The value could not be found.", vbCritical, "Get Column Number"
    Else
        MsgBox "The column number is '" & ColNum & "'.", vbInformation, "Get Column Number"
    End If
    
End Sub

Sub Month1AM2()
    ' Application.Match: IsNumeric
    
    Dim ColNum As Variant
    
    ColNum = Application.Match(Sheets("Std").Range("F2").Value, Sheets("SAN-68855").Rows(3), 0)
    If IsNumeric(ColNum) Then
        MsgBox "The column number is '" & ColNum & "'.", vbInformation, "Get Column Number"
    Else
        MsgBox "The value could not be found.", vbCritical, "Get Column Number"
    End If
    
End Sub
user_98140690
Related