vba 对象'_Global'的方法'Cells'失败VBA
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21678304/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Method 'Cells' of Object'_Global' failed VBA
提问by user3041384
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
编辑：我有一个访问应用程序，我需要在其中打开一个现有的 Excel 工作表，找到一个日期（已经在工作表中）并用 1 或 0 填充一行（带有日期单元格列）
this means i have to convert the datecell.column to the alphabet equivalent before i can set a cell to be populated.
这意味着我必须将 datecell.column 转换为等效的字母表，然后才能设置要填充的单元格。
I used a function, which i have used in excel before (as seen below) END EDIT
我使用了一个函数，我之前在 excel 中使用过（如下所示）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
当我运行它时，有时它运行得很好，有时我得到 Object'_Global' 的 Method 'Cells' failed 错误代码，当我单击 debug 时，它会突出显示 col_letter 函数的第三行
vArr = Split(Cells(1, lngcol).Address(True, False), "$")
do you have a clue to why it (seemingly) randomly chooses to display this error?
你知道为什么它（似乎）随机选择显示这个错误？
回答by Siddharth Rout
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
如果 find 无法返回任何内容怎么办？您可能想要使用
If Not f is Nothing then
'Rest of the code
End If
回答by user3041384
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.
@Siddarth 指出我的主要问题是我没有正确（明确地）引用单元格，这是一个主要问题，因为我正在从 Access 调用函数（可以在 Excel 工作表上运行）。
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...
并且由于它不需要来自 excel 的任何单元格，因此在这里可以正常工作...
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.
我觉得这会使代码执行速度变慢（这可能只是我的系统），因此如果您仍有建议，请随时发布答案。
