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.
I am getting an automation error at:
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...
Code:
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:
Code:
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...
Last edited: