将Find VBA函数与定义的字符串一起使用

问题描述：

嘿，也许我在这里看不到明显的东西，但是如何使用带有预定义变量的Find VBA函数.我使用的是从用户表单分配的字符串的串联，只是在其前面的总计"，但我无法返回该行.

Hey maybe I'm not seeing something obvious here but how can you use the Find VBA function with a predefined variable. I'm using a concatenation of a string assigned from a user form and just "total " in front of it, yet I can't return the row.

下面是我的代码

Dim HBWS As Worksheet
Dim TickerString As String
TickerString = "Total " & TTB
Set HBWS = Sheets("Hoenheimm Worksheet")


BorrowColumn = HBWS.Cells.Find(What:="Borrow").Column 'Works just fine
TickerRow = HBWS.Cells.Find(What:=TickerString).Row 'Throws an error

请注意，TTB设置为股票行情自动收录器. AAPL，我可以在本地窗口中检查Tickerstring实际上是否等于"Total AAPL"

Note that TTB is set to a ticker ex. AAPL, and I can check in my local windows that Tickerstring is in fact = to "Total AAPL"

我希望.Row列为我提供工作表中该字符串所在位置的行.

I would expect the .Row column to give me the row on my worksheet as to where this string is located.

抛出的错误如下...

the error being thrown is as follows...

运行时错误'91':

"Run-Time error '91':

对象变量或未设置块变量"

Object Variable or With block variable not set"

任何经历，谢谢

答

您正在调用Range.Find.该方法返回Range对象引用-当找不到要查找的内容时，它返回Nothing，即 null引用.

You're invoking Range.Find. That method returns a Range object reference - and when it does not find what it's told to look for, it returns Nothing, i.e. a null reference.

TickerRow = HBWS.Cells.Find(What:=TickerString).Row 'Throws an error

该代码的工作方式(及其上方的工作指令)是假设 Find返回有效的对象引用.

What this code is doing (and the working instruction just above it), is assuming that Find returns a valid object reference.

显然HBWS.Cells不包含"Total " & TTB(无论TTB是什么)，因此您的代码正在有效地尝试针对Nothing的Range引用调用Range.Row ...这是非法的，并且遇到错误时会引发运行时错误91.

Apparently HBWS.Cells does not contain "Total " & TTB (whatever TTB is), so your code is effectively trying to invoke Range.Row against a Range reference that's Nothing... which is illegal, and raises run-time error 91 as you're experiencing.

您永远都不应该假设Find会返回有效的引用.拆分它，并使用If ... Is Nothing检查来验证返回的对象引用:

You shouldn't assume that Find will return a valid reference, ever. Split it up, and validate the returned object reference with an If ... Is Nothing check:

Set tickerResult = HBWS.Cells.Find(What:=TickerString)
If Not tickerResult Is Nothing Then
    tickerRow = tickerResult.Row
Else
    'tickerString was not found. 
    'watch for leading/trailing/extra spaces if the string does *look* legit.
End If

在调用Range.Find时，应始终为可选参数提供一个值，因为其实现会记住"先前调用中的值，并且容易出错.

When calling Range.Find, you should always provide a value for the optional parameters, because its implementation "remembers" values from previous invocations, and this is easily bug-prone.

将Find VBA函数与定义的字符串一起使用

相关推荐