I'm trying to find the column index in the header row and keep getting search string not found. My understanding, right or wrong, is that columns 1 through 26 in row 1 are to be searched.
Do I have the Range expression incorrect? Some other problem? (I know "Last Name" is in column 12)
Code:
Public Sub ExcelFindCol()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'* Get column index number given the column name. (MOD UNDER CONSTRUCTION)
'*
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim MyCol As Variant
Dim xlsApp As Object
Dim errMsg As String
Dim SearchTerm As String
Set xlsApp = CreateObject("Excel.Application")
On Error GoTo HandleErr
xlsApp.Visible = False
xlsApp.Workbooks.Open "c:\eRep\PerCapTestFile.xlsx", True, False
SearchTerm = "Last Name"
MyCol = xlsApp.WorksheetFunction.MATCH(SearchTerm, xlsApp.Range("1,1:26"), 0)
MsgBox MyCol
HandleErr_exit:
'clean up
xlsApp.Workbooks.Close
xlsApp.Quit
Set xlsApp = Nothing
Exit Sub
HandleErr:
If Err.Number <> 0 Then
Select Case Err.Number
Case 1004 'Excel error when Match term is not found
MsgBox "No Match for " & SearchTerm
Case Else
errMsg = "Error number: " & Str(Err.Number) & vbNewLine & _
"Source: " & Err.Source & vbNewLine & _
"Description: " & Err.Description
Debug.Print errMsg
End Select
Err.Clear
End If
Resume HandleErr_exit
End Sub