How to get column number in Excel VBA using regex?

Question

I know how to return column number using .Match, .Find and with iteration (yuck).

Is it possible to have it done more quickly using regex?

Answer 1

Try Debug.Print Cells(5,7).Column, it'll output 7!

Here is an example of a .Find method and it'll output the column of the found cells!

Sub test_Eswemenasja()
Dim FirstAddress As String, cF As Range, SearchedStr as String
SearchedStr = "Cat"

ActiveSheet.Range("A1").Activate
With ActiveSheet.Cells
    'First, define properly the Find method
    Set cF = .Find(What:=SearchedStr, _
                After:=ActiveCell, _
                LookIn:=xlFormulas, _
                LookAt:=xlPart, _
                SearchOrder:=xlByColumns, _
                SearchDirection:=xlNext, _
                MatchCase:=False, _
                SearchFormat:=False)

    'If there is a result, keep looking with FindNext method
    If Not cF Is Nothing Then
        FirstAddress = cF.Address
        Do
            'Message with the column number!
            MsgBox "Found in column : " & cF.column
            Set cF = .FindNext(cF)
        'Look until you find again the first result
        Loop While Not cF Is Nothing And cF.Address <> FirstAddress
    End If
End With

End Sub