Method 'Cells' of Object'_Global' failed VBA

Question

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?

Siddharth Rout · Answer 1 · 2014-02-10T14:13:56.093

1

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

edited Feb 10 '14 at 14:13

answered Feb 10 '14 at 13:17

Siddharth Rout

147,039
17
206
250

ok thanks. the worksheet names are variables which are gotten in one of the steps in the subroutin which is why i couldnt use it in the function (thats for your first suggestion) – user3041384 Feb 10 '14 at 13:30
i used your 2nd suggestion and ran the code twice. it worked the first time and gave the same error the second time. i did "Set f = book.Worksheets(a).Range("5:5").Find(strd) If Not f Is Nothing Then f1 = f.Column f2 = Col_Letter(f1) g = f2 & Srow End If book.Worksheets(a).Range(g).Value = "0" " – user3041384 Feb 10 '14 at 13:34
@user3041384: You need to understand what the function does. It splits the cells for example `$A$1` to get the Column Number. So it really doesn't matter if you don't supply a sheet name. Every workbook has minimum 1 sheet and hence we use `Sheets(1)` Regarding your 2nd comment, what error did you get and on what line? – Siddharth Rout Feb 10 '14 at 13:39
@user3041384: By the way you can use `Split(Thisworkbook.Sheets(1).Cells(, lngcol).Address, "$")(1)` as well as shown [HERE](http://stackoverflow.com/questions/10106465/excel-column-number-from-column-name/10107264#10107264) – Siddharth Rout Feb 10 '14 at 13:41
tried your first suggestion and got Method 'ThisWorkbook' of Object'_Global' failed – user3041384 Feb 10 '14 at 13:41
@user3041384: that is strange as it works for me. What is the value of `lngcol`? – Siddharth Rout Feb 10 '14 at 13:44
let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/47173/discussion-between-siddharth-rout-and-user3041384) – Siddharth Rout Feb 10 '14 at 13:45
ok so basically the codes are written in access and then there's a code that opens excel and (Set book = app.Workbooks.Add("D:\Updated\...timesheeteng.xlsm") ) and i work on the excel from access using book.worksheet(a)... etc. maybe thats why your first suggetin didnt work?
for the second suggetion i got the same method cells of object global failed – user3041384 Feb 10 '14 at 13:47
@user3041384: Ah! In that case replace `ThisWorkbook` with `book` :) – Siddharth Rout Feb 10 '14 at 13:48

score 0 · Answer 2 · answered Feb 11 '14 at 12:41

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.

Method 'Cells' of Object'_Global' failed VBA

2 Answers2

Linked