Automation error and/or subscript out of range

jbryan10

New Member
Joined
May 16, 2012
Messages
7
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.

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:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,813
Do the sheet names match "Client Names" exactly (including the number of spaces)?

Is the CompName actualy in row 1 of Client Names?
 

jbryan10

New Member
Joined
May 16, 2012
Messages
7
Do the sheet names match "Client Names" exactly (including the number of spaces)?

Is the CompName actualy in row 1 of Client Names?
yes on the "Client Names" matching exactly. It works just fine in:

Code:
CompCol = wb.Application.WorksheetFunction.Match(CompName, wb.Sheets("Client Names").Rows(1), 0)
CompName is in the other workbook and is defined just before the database file is opened.
 

jbryan10

New Member
Joined
May 16, 2012
Messages
7
The workbook is opened as NOT read only. I went ahead and tried opening it as read only and got the same error.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
22,813
Remove the OnError Resume Next and step through the code. I'm suspecting that there are trailing spaces or such that mess up the Match.
 

jbryan10

New Member
Joined
May 16, 2012
Messages
7
That is there because CompName will not always have a match

Code:
CompCol = 0
On Error Resume Next
CompCol = wb.Application.WorksheetFunction.Match(CompName, wb.Sheets("Client Names").Rows(1), 0)
On Error GoTo 0
If CompCol <> 0 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
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
It's understood WHY you have the On Error Resume Next statement in there.
Mike suggested removing it as a troubleshooting step only, not as resolution...

Also, try Changing

Application.WorksheetFunction.Match
to
Application.Match

And change
If CompCol <> 0 Then
to
If Not IseError(CompCol) Then


And Dim CompCol As Variant
 

jbryan10

New Member
Joined
May 16, 2012
Messages
7
Same problems. I also took the Match line out completely and still get the error.
 

jbryan10

New Member
Joined
May 16, 2012
Messages
7
I moved the LastClient line to the beginning (just after the workbook is opened) and it made it past just fine. Same error at the ClientName.AddItem line.
 

Forum statistics

Threads
1,082,500
Messages
5,365,935
Members
400,863
Latest member
kimtid

Some videos you may like

This Week's Hot Topics

Top