CODEWITHSUNDEEP
excelvba
Gun hanwei
Gun hanwei

Reputation: 93

Find index of column by using name

Option Explicit

Function FindIndexCol(rngIndexRow, rngIndexCol) As String

    Set rngIndexRow = Worksheets("sheet1").Range(rngIndexRow)
    Set rngIndexCol = rngIndexRow.Find(rngIndexCol)

    FindIndexCol = Split(Cells(1, rngIndexCol.Column).Address, "$")(1)
End Function


Sub Test()
    Dim Purchasing_Document, Backup_Purchasing_Document  As String

    Purchasing_Document = FindIndexCol("1:1", "Purchasing Document")
    Backup_Purchasing_Document = FindIndexCol("1:1", "Backup Purchasing Document")
End Sub

I am using FindIndexCol() function to get the index of the column by name. Purchasing_Document should return me the column A and Backup_Purchasing_Document will return me column K. But both of them are returning me character K. How should I change the above code?

Upvotes: 1

Views: 75

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57753

Note that if you declare Dim Purchasing_Document, Backup_Purchasing_Document As String only the last variable is a String but the first is a Variant. In VBA you need to specify a type for every variable: Dim Purchasing_Document As String, Backup_Purchasing_Document As String.

The documentation of the Range.Find method states:

The settings for LookIn, LookAt, SearchOrder, and MatchByte are saved each time you use this method. If you do not specify values for these arguments the next time you call the method, the saved values are used.

So if using Find you should at least specify these 4 parameters or you cannot predict which setting Find is using for these parameters.

Also LookAt:=xlWhole is necessary to distinguish between "Purchasing Document" and "Backup Purchasing Document" because the first is part of the second.

So at least do the following:

Public Function FindIndexCol(ByVal IndexRow As String, ByVal IndexCol As String) As String
    Dim rngIndexCol As Range
    Set rngIndexRow = Worksheets("sheet1").Range(IndexRow)

    Dim rngIndexCol As Range
    Set rngIndexCol = rngIndexRow.Find(What:=IndexCol, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchByte:=False)
    
    If Not rngIndexCol Is Nothing Then
        FindIndexCol = Split(Cells(1, rngIndexCol.Column).Address, "$")(1)
    Else
        'output some error if nothing was found
        MsgBox "Could not find '" & IndexCol & "' in '" & IndexRow & "'.", vbCritical
        'or return some error at least
        'FindIndexCol = "Column not found"
    End If
End Function


Public Sub Test()
    Dim Purchasing_Document As String, Backup_Purchasing_Document As String

    Purchasing_Document = FindIndexCol("1:1", "Purchasing Document")
    Backup_Purchasing_Document = FindIndexCol("1:1", "Backup Purchasing Document")
End Sub

Note that FindIndexCol only works in sheet1 because of this line

Set rngIndexRow = Worksheets("sheet1").Range(rngIndexRow)

Therefore I suggest to make it more useful by making it more generic

Public Function FindIndexCol(ByVal rngIndexRow As Range, ByVal ColumnName As String) As String
    Dim rngIndexCol As Range
    Set rngIndexCol = rngIndexRow.Find(What:=ColumnName, LookIn:=xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByColumns, MatchByte:=False)
    
    If Not rngIndexCol Is Nothing Then
        FindIndexCol = Split(rngIndexRow.Parent.Cells(1, rngIndexCol.Column).Address, "$")(1)
    Else
        'output some error if nothing was found
        MsgBox "Could not find '" & ColumnName & "' in '" & rngIndexRow.Address(External:=True) & "'.", vbCritical
        'or return some error at least
        'FindIndexCol = "Column not found"
    End If
End Function


Public Sub Test()
    Dim Purchasing_Document As String, Backup_Purchasing_Document As String
    
    With Worksheets("sheet1")
        Purchasing_Document = FindIndexCol(.Rows(1), "Purchasing Document")
        Backup_Purchasing_Document = FindIndexCol(.Rows(1), "Backup Purchasing Document")
    End With
End Sub

Upvotes: 2

Related Questions