VBA-使用Find()获取列号，获取错误91-对象var.或与块变种.没有设置 [英] VBA - getting column # with Find(), get error 91 - object var. or with block var. not set

问题描述

我有一个VBA脚本，其中我试图使用find()来获取单独工作表(同一工作簿)上一列的列号.

I have a VBA script wherein I am trying to use find() to get the column number of a column on a separate worksheet (same workbook).

因此，我从"Sheet 1"运行此宏，以找到"Sheet 2"上在第1行中带有单词"Ins Val"的列.奇怪的是，在宏的前面，我使用了相同的公式获得没有问题的列号.但是，下面的代码抛出运行时错误91，对象变量或未设置块"，但我不知道为什么.

So, from "Sheet 1" I run this macro, to find the column on "Sheet 2" that has the word "Ins Val" in row 1. What's odd is that earlier in the macro, I use the same formula to get a column number without issue. However, the below code throws a "Run Time Error 91, Object Variable or With Block Not Set" but I can't figure why.

dim useDataWS as Worksheet, typeValColumn as Integer, theType as String, mainWS as Worksheet

Set mainWS = worksheets("Sheet 1")
Set useDataWS = worksheets("Sheet 2")

theType = mainWS.Cells(49,5).Value
'' the below line gives the error
typeValColumn = useDataWS.rows(1).Find(what:=theType, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

但是，在该宏的前面，我做了同样的事情-没有错误:?

But, earlier in that macro, I do the same thing- with no error :?

With useDataWS
        noOneCol = .Rows(1).Find(what:=theType, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column
End With

注意:即使我将第一部分更改为使用"with"，也会出现相同的错误.

Note: Even if I change that first part to use "with", the same error occurrs.

有什么想法吗?(注意:这是一个更健壮的脚本，但是我尝试获取适用的部分.如果还有其他原因可能导致这种情况，请让我知道我正在做的其他事情可能会弄乱这一点，我会发布更多代码).

Any ideas? (Note: It's a more robust script, but I tried to get the parts that apply. If there's something else that might be causing this, let me know what other kinds of things I'm doing that could mess this up and I'll post more of the code).

谢谢@BranislavKollár-使用"xlFormulas"而不是"xlValues"进行搜索可以解决此问题.(尽管我仍然不知道为什么Excel会使用值"引发错误，但嘿-它起作用了！)

edit: Thanks @Branislav Kollár - using search by "xlFormulas" instead of "xlValues" solves the issue. (Although I still don't know why Excel throws the error with 'values', but hey - it works!)

推荐答案

原因，为何以下行

typeValColumn = useDataWS.rows(1).Find(what:= theType，LookIn:= xlValues，lookat:= xlWhole，MatchCase:= False).列

出现错误是因为 Find()方法发现 Nothing ，并将 .column 应用于 Nothing 会导致错误.

was giving an error is because the Find()method found Nothing, and applying .column to a Nothing results in error.

解决方案可以将 LookIn:= xlValues 替换为 LookIn:= xlFormulas .

Solution can be replacing LookIn:=xlValues with LookIn:=xlFormulas.

我想提供更多有关它为什么起作用的信息，但我并不诚实.我发现的一件事是 LookIn:= xlFormulas 甚至可以找到隐藏的单元格，而且我猜它具有更广泛的用途.

I'd like to provide more info about why it does work, but I don't know honestly. One thing I found out is that LookIn:=xlFormulas will find even hidden cells and I guess it has more general usage.

有关 Find()方法的更多信息，可以在在Excel VBA中查找方法或.在Excel VBA中查找和.FindNext

More about Find() method can be found on MSDN Range.Find Method, or Find Method in Excel VBA or .Find and .FindNext in Excel VBA

这篇关于VBA-使用Find()获取列号，获取错误91-对象var.或与块变种.没有设置的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！