Automation error and/or subscript out of range

[jbryan10]

I am completely baffled by these errors I am getting. Below is a short snippet of my code. I am using 2 separate workbooks to fill comboboxes in a userform. One of the workbooks is already open, so I am opening the other and reading info from it.

Set wb = Workbooks.Open("X:\PERSONNEL\jbryan\AutoCert\Database.xlsx", True, False)
                
    With wb.Sheets("Employees")
       bunch of code
    End With
    
    With wb.Worksheets("Company Addresses")
        bunch of code
        On Error Resume Next
             CompCol = wb.Application.WorksheetFunction.Match(CompName, wb.Sheets("Client Names").Rows(1), 0)
        On Error GoTo 0
    End With
    
    LastClient = wb.Worksheets("Client Names").Cells(2, CompCol).End(xlDown).Row
    For i = 2 To LastClient
         ClientName.AddItem wb.Worksheets("Client Names").Cells(i, CompCol)
    Next i

I am getting an automation error at:

LastClient = wb.Worksheets("Client Names").Cells(2, CompCol).End(xlDown).Row

I have tried all the different variations I could think of, such as:

replacing wb with Workbooks("Database.xlsx") or Workbooks("X:\PERSONNEL\jbryan\AutoCert\Database.xlsx") both of which result in a subscript out of range error at that line (yes, the "Client Names" tab does exist within Database.xlsx)

making it a with statement resulted in the automation error at the with statement

adding wb.Activate and replacing wb with ActiveWorkbook which just moves the automation error to wb.Activate if it is placed just before the if statement

moving the if statement inside the with statement above it.

moving the if statement inside the with statement above it and moving that with statement above the other.

I just don't see what I am missing...

 

[jbryan10]

Ok, i did some more experimenting and think I figured out what was happening (still don't know why)

        CompanyName = CompName
        On Error Resume Next
        CompCol = wb.Application.Match(CompName, wb.Sheets("Client Names").Rows(1), 0)
        On Error GoTo 0
        If Not IsError(CompCol) Then
            LastClient = wb.Sheets("Client Names").Cells(2, CompCol).End(xlDown).Row
            For i = 2 To LastClient
                ClientName.AddItem wb.Worksheets("Client Names").Cells(i, CompCol)
            Next i
        End If

The problem is with the CompanyName = CompName.
CompanyName is a combobox in the userform filled by the array: CompNames. That line selects the CompName index so the box is already filled.

If the line is taken out or moved after the troublesome bit of code, it works just fine.