Results 1 to 4 of 4
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Find column index in Excel file

    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

  2. #2
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I added worksheet name to the search statement but it didn't seem to matter. Without it, I don't know which sheet of 3 sheets in the file would be searched.

    Code:
    MyCol = xlsApp.Worksheet("Membership").WorksheetFunction.MATCH(SearchTerm, xlsApp.Range("1,1:26"), 0)

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    I added worksheet name to the search statement but it didn't seem to matter. Without it, I don't know which sheet of 3 sheets in the file would be searched.

    Code:
    MyCol = xlsApp.Worksheet("Membership").WorksheetFunction.MATCH(SearchTerm, xlsApp.Range("1,1:26"), 0)

  4. #4
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Solution is to use the rows object:

    Code:
    MyCol = xlsApp.Application.Match(SearchTerm, xlsApp.Rows(1), 0)

Please reply to this thread with any new information or opinions.

Similar Threads

  1. VBA cannot find Excel file
    By Lou_Reed in forum Access
    Replies: 1
    Last Post: 12-01-2017, 01:13 PM
  2. Replies: 16
    Last Post: 05-29-2017, 08:44 PM
  3. Replies: 2
    Last Post: 05-24-2016, 04:54 PM
  4. Find and remove filters from an Excel file
    By nagiese in forum Programming
    Replies: 1
    Last Post: 11-03-2015, 04:54 PM
  5. Replies: 20
    Last Post: 02-02-2015, 03:02 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums