VBA in Access 2010 using Split function sometimes gives error 1004

I am using the Split function to find a named field in Access using VBA on an Excel spreadsheet. The issue is sometimes it errors out giving Error 1004. Application-defined or object-defined error. Sometimes it works and sometimes it doesn't.. I'm the only one with access to the spreadsheets. To be sure the files aren't open I reboot my computer. It gets to the line coded as "FindColumnName2 = Split(Cells(1, j).Address, "$")(1)"  and immediately errors out.

Any ideas?
'** This will find the Colum Name in an Excel spread sheet
'**  Send the fully equated Excel file and the Cell Name and boolean if cell was found or not.
'**  If the cell was not found prompt if you want to continue with the next file.

 Dim excelFile As String, iRow As Integer
 Dim xlObj As Object, j As Integer, colCount As Integer
 Dim vArr
 excelFile = xcel_file
 iRow = 1
 Set xlObj = CreateObject("excel.application")
       xlObj.Workbooks.Open excelFile
       With xlObj
 '      .Visible = True
             .DisplayAlerts = False
             .Worksheets("Worksheet1").AutoFilterMode = False
             colCount = .ActiveSheet.UsedRange.Columns.Count
             For j = 1 To colCount
                  If .ActiveSheet.Cells(iRow, j).Value = Trim(name_of_cell) Then
                   FindColumnName2 = Split(Cells(1, j).Address, "$")(1)
                    GoTo EXIT_FUNCTION
'                    blnCellFound = True
                  End If
            Next j

Open in new window

Chuck LoweAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
I would:

1. Make it visible.
2. When the error occurs, see what's being referenced by the split() and whether it's valid or not.

Don't see anything wrong with the code per say, so it must be the data in the cell.

If you can't avoid the problem, consider switching to in-line error handling and ignoring the 1004 when it occurs.

This expression:
Split(Cells(1, j).Address, "$")(1)

Open in new window

assumes (requires) that there is a "$" in the string being split.
Martin LissOlder than dirtCommented:
@aikimark: What he's splitting is the address of the cell and AFAIK addresses of referenced cells always contain 2 dollar signs, so what the split() line is doing is returning (or trying to at least) the column letter.

@Chuck Lowe: If your error is reproducible, put a breakpoint on the offending line, run the sub and when the code gets there place your cursor on "j" and see what the value is. Also highlight Cells(1, j).Address and see what the address is. One or the other should help point out the problem. BTW another way to get the column letter is by way of this function:
Function ColumnLetter(lngCol As Long) As String
' Convert a number to a column letter. For example 1 -> A, 2 -> B, etc.
If lngCol > 26 Then
    ColumnLetter = Chr(Int((lngCol - 1) / 26) + 64) & Chr(((lngCol - 1) Mod 26) + 65)
    ColumnLetter = Chr(lngCol + 64)
End If
End Function

Open in new window

To use it change your Dim of 'j' to j As Long and change line 28 to

FindColumnName2 = ColumnLetter(j)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Chuck LoweAuthor Commented:
So what should be in the code instead of a "$" ?
Martin LissOlder than dirtCommented:
If your cell address is $A$1 and you want to return "A" then your code should work as is. Did you try my debugging suggestion?
Chuck LoweAuthor Commented:
@Martin Liss: I tried the debugging as you suggested. I took off the On Error and it returns the column "K" or what ever it is. There is no "1" or "$" returned. But when I turn the Error statement back on It errors out immediately. I used your Function ColumnLetter and it works like a charm.

Thanks! It's a lot less coding and allows for unlimited number of cell ranges.
Martin LissOlder than dirtCommented:
You're welcome and I'm glad I was able to help.

In my profile you'll find links to some articles I've written that may interest you.
Marty - MVP 2009 to 2015, Experts-Exchange Top Expert Visual Basic Classic 2012 to 2014
Chuck LoweAuthor Commented:
Thanks. I'll check it out.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.