VBA to find a column number with match function

Question

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)

Kin Siang · Accepted Answer · 2021-05-28T06:36:01.897

0

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

Origninal data:

Sub colNum()

Dim rowNum As Long

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

End Sub

edited May 28 '21 at 06:36

answered May 28 '21 at 05:47

Kin Siang

2,644
2
4
8

`Application` is not necessary. Try without it. – VBasic2008 May 28 '21 at 06:29
Ya, without applicaton it work, probably his issue is how he managing his data – Kin Siang May 28 '21 at 06:35
After fixing the rows, change it to "dim as long" instead of interger, and remove ".value". it's working now – Hazel May 28 '21 at 07:35
Here is one reference you can check regarding long vs integer variable. https://stackoverflow.com/questions/26409117/why-use-integer-instead-of-long#:~:text=Traditionally%2C%20VBA%20programmers%20have%20used,re%20declared%20as%20type%20Integer.&text=So%2C%20in%20summary%2C%20there's%20almost,an%20Integer%20type%20these%20days. In summary, declared integer and VBA will auto convert it to long type, therefore long is default type for me in most case – Kin Siang May 28 '21 at 07:39

score 0 · Answer 2 · answered May 28 '21 at 06:44

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

VBA to find a column number with match function

2 Answers2

Match in VBA