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

但是，所述变量不是对象，所以这样做会引发需要对象"编译错误.

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屋！