Reputation: 93
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
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
, andMatchByte
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