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:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Do the sheet names match "Client Names" exactly (including the number of spaces)?

Is the CompName actualy in row 1 of Client Names?
 
Upvote 0
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.
 
Upvote 0
The workbook is opened as NOT read only. I went ahead and tried opening it as read only and got the same error.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,491
Messages
6,113,963
Members
448,536
Latest member
CantExcel123

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top