当没有Object时，VBA for Excel会抛出“对象变量或未设置块变量”

In my code, I have declared these variables:

在我的代码中，我声明了这些变量：

Dim Field_Name, Datatype, row As Integer

Then, inside a For loop, I have this code:

然后，在For循环中，我有这个代码：

Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
row = Worksheets(i).UsedRange.Find("Field Name").row + 1

However, that code throws the "Object variable or with block variable not set" run-time error. According to the API, the Range.Column and Range.row property is a read-only Long. I have tried making the datatype of my variables to Long, but with no success. It would appear that VBA expecting me to do

但是，该代码抛出“对象变量或未设置块变量”运行时错误。根据API，Range.Column和Range.row属性是只读的Long。我已经尝试将我的变量的数据类型设置为Long，但没有成功。 VBA似乎希望我这样做

Set Field_Name = Worksheets(i).UsedRange.Find("Field Name").Column
Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column
Set row = Worksheets(i).UsedRange.Find("Field Name").row + 1

However, said variables are not objects, so doing that throws the "Object required" compile error.

但是，所述变量不是对象，因此这样做会抛出“Object required”编译错误。

Any help with this would be greatly appreciated. If you're not sure about how to fix it, then any workarounds or alternative ways to get the column number and row number of a cell would be greatly appreciated.

任何有关这方面的帮助将不胜感激。如果您不确定如何解决它，那么任何解决方法或获取单元格的列号和行号的替代方法将非常感激。

3 个解决方案

#1

Even though this is an old question, I'd like to say something too.

即使这是一个老问题，我也想说些什么。

I had the same problem to get this error while using the .Find method. I came to this question and so others will do the same.

使用.Find方法时，我遇到了同样的问题。我来到这个问题，所以其他人也会这样做。

I found a simple solution to the problem:

我找到了一个解决问题的简单方法：

When Find does not find the specified string it returns Nothing. Calling anything directly after Find will lead to this error. So, your .Column or .row will throw an error.

当Find找不到指定的字符串时，它返回Nothing。在查找之后直接调用任何内容将导致此错误。因此，您的.Column或.row将抛出错误。

In my case I wanted an Offset of the found cell and solved it this way:

在我的情况下，我想要找到找到的单元格的偏移并以这种方式解决它：

Set result = Worksheets(i).Range("A:A").Find(string)
    If result Is Nothing Then
        'some code here
    ElseIf IsEmpty(result.Offset(0, 2)) Then
        'some code here
    Else
        'some code here
    End If

#2

Simplified answer:

简化回答：

Your .Find call is throwing the error.

你的.Find电话正在抛出错误。

Simply adding "Set " to that line will address the problem. i.e...

只需在该行添加“Set”即可解决问题。即...

Set Datatype = Worksheets(i).UsedRange.Find("Datatype").Column

Without "Set," you are attempting to assign "nothing" to a variable. "Nothing" can only be assigned to an object.

没有“设置”，您试图为变量分配“无”。 “Nothing”只能分配给一个对象。

You can stop reading here unless you would like to understand what all the other (valid, worthwhile) fuss was about your code.

你可以在这里停止阅读，除非你想了解所有其他（有效的，有价值的）大惊小怪的代码。

To paraphrase all of the (warranted) code critiquing, your Dim statement is bad. The first two variables are not being "typed" and end up as variants. Ironically, this is why the solution I just described works.

为了解释所有（保证的）代码批评，你的Dim声明很糟糕。前两个变量没有被“打字”，最终变成了变体。具有讽刺意味的是，这就是我刚才描述的解决方案的原因

If you do decide to clean up that Dim statement, declare DataType as a variant...

如果您决定清理该Dim语句，请将DataType声明为变体...

Dim DataType as variant

#3

What about the below code:

以下代码如何：

    For i = 1 to 1 ' change to the number of sheets in the workbook
    Set oLookin1 = Worksheets(i).UsedRange
    sLookFor1 = "Field Name"
    Set oFound1 = oLookin1.Find(What:=sLookFor1, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound1 Is Nothing Then
    Field_Name = oFound1.Column
    RRow = oFound1.Row +1

' code goes here

    Else
    Msgbox "Field Name was not found in Sheet #" & i
    End If

    Set oLookin2 = Worksheets(i).UsedRange
    sLookFor2 = "Datatype"
    Set oFound2 = oLookin2.Find(What:=sLookFor2, LookIn:=xlValues, LookAt:=xlWhole, MatchCase:=False)

    If Not oFound2 Is Nothing Then
    DataType = oFound2.Column

' code goes here

    Else
    Msgbox "Datatype was not found in Sheet #" & i
    End If
    Next i

#1