使用定义字符串的 Find VBA 函数 [英] Using the Find VBA function with Defined String

问题描述

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

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 设置为股票代码 ex.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.

抛出的错误如下...

运行时错误'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 是什么)，因此您的代码有效地尝试针对 Range 引用调用 Range.Row 即 >Nothing...这是非法的，并且在您遇到时会引发运行时错误 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 函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！