Method 'Cells' of Object'_Global'

Question

This question already has an answer here:

• Excel VBA Method 'Range' of object'_global' failed error 1004 1 answer

EDIT: i have an access application in which i need to open an existing excel sheet, find a date (already in the sheet) and populate a row (with the date cell column) with either 1 or 0

this means i have to convert the datecell.column to the alphabet equivalent before i can set a cell to be populated.

I used a function, which i have used in excel before (as seen below) END EDIT

Function Col_Letter(lngcol) As String
    Dim vArr
    vArr = Split(Cells(1, lngcol).Address(True, False), "$")
    Col_Letter = vArr(0)
    End Function

the code below is an example of how i use the code in my application

Dim  CD, d, f, f1, f2, g, strd
  ...
            For n = 0 To 10
            d = CD + n
            strd = Str(d)
            Select Case Weekday(strd)
            Case vbSunday, vbSaturday

            Case Else
            Set f = book.Worksheets(a).Range("5:5").Find(strd)
            f1 = f.Column
            f2 = Col_Letter(f1)
            g = f2 & Srow
            book.Worksheets(a).Range(g).Value = "0"
            End Select
            Next n
        End If
'CD = Current date, a = worksheetname set eariler in code, srow = excel row number set earlier in code`enter code here`
'this is executed in an excel sheet which was opened from access

when i run this, sometimes it runs perfectly and other times i get the Method 'Cells' of Object'_Global' failed error code and when i click debug it highlights the third line of the col_letter function

vArr = Split(Cells(1, lngcol).Address(True, False), "$")

do you have a clue to why it (seemingly) randomly chooses to display this error?

Answer 1

so just incase you've come to this page with a similar problem here's how i fixed it.

@Siddarth pointed out my main problem was that i wasnt refering to the cells properly (explicitly) and this is a major problem since i am calling the function (which would work on an Excel sheet) from Access.

still after i tried various ways of qualifying the cell, code still wouldnt work properly therefore i decided to use another function for changing column number to letter... i used

Function ColumnLetter(ColumnNumber) As String
    Dim n As Integer
    Dim c As Byte
    Dim s As String

n = ColumnNumber
Do
    c = ((n - 1) Mod 26)
    s = Chr(c + 65) & s
    n = (n - c) \ 26
Loop While n > 0
ColumnLetter = s
End Function

and since it doesnt require any cells from excel it works fine here...

i feel it makes the code execute a bit slower (that could just be my system) so if you still have suggestions feel free to post an answer.

Answer 2

You need to fully qualify the cells object. Try this

Function Col_Letter(lngcol As Integer) As String
    Col_Letter = Split(book.Sheets(1).Cells(, lngcol).Address, "$")(1)
End Function

or

Function Col_Letter(lngcol As Integer) As String
    Col_Letter = Split(book.ActiveSheet.Cells(, lngcol).Address, "$")(1)
End Function

The other error you may get is because of this line

f1 = f.Column

What if the find is not able to return anything? You may want to use

If Not f is Nothing then
    'Rest of the code
End If