VBA for Excel抛出“对象变量或未设置块变量".没有物体时 [英] VBA for Excel throws "Object variable or with block variable not set" when there is no Object

问题描述

在我的代码中，我已经声明了以下变量:

In my code, I have declared these variables:

Dim Field_Name, Datatype, row As Integer

然后，在For循环中，我有以下代码:

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

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

但是，该代码将引发对象变量或未设置块变量"的运行时错误.根据API，Range.Column和Range.row属性是只读的Long.我尝试将变量的数据类型设置为Long，但没有成功.看来VBA希望我这样做

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

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

但是，所说的变量不是对象，因此这样做会抛出"Object required"编译错误.

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

任何对此的帮助将不胜感激.如果您不确定如何解决此问题，那么将非常感谢您获取单元格的列号和行号的任何变通方法或替代方法.

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.

推荐答案

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

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

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

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.

我找到了解决该问题的简单方法:

I found a simple solution to the problem:

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

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.

在我的情况下，我想要找到的单元格的Offset并通过以下方式解决它:

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

这篇关于VBA for Excel抛出“对象变量或未设置块变量".没有物体时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！