Object'_Global'失败VBA的方法'细胞' [英] Method 'Cells' of Object'_Global' failed VBA

问题描述

编辑：我有一个访问应用程序中，我需要打开一个现有的Excel工作表，找到一个日期（已经在表）和填充一行（带日期的单元格列）用1或0

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

这意味着我必须在datecell.column转换为字母相当于之前，我可以设置一个单元格填充。

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

我使用的功能，这是我在Excel中使用之前（如下图所示），编辑完

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

在code以下是我如何使用code在我的应用程序的示例

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

当我运行它，有时它运行完美，其他时候，我得到的Object'_Global失败的错误code中的方法'细胞'，当我点击调试它突出了col_letter功能
的第三行

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?

推荐答案

您需要完全限定的细胞对象。试试这个

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

或

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

这篇关于Object'_Global'失败VBA的方法'细胞'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！