我有两个Sub过程,它们都太长了,无法完全粘贴到这里,但是我已经将代码块缩小到以下导致错误的函数和调用过程。
我能够毫无问题地运行子 1 然后运行子2,然后当我尝试重新运行子 1 时,我在子 1 中的这一行上收到一个object variable not set
错误:
cName = "Current Price"
cG = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
"当前价格"拼写正确,是每个工作表上的列标题作为参数传递,并且Sub 1第一次运行良好,这可能是公开声明的问题吗?该错误归因于以下IsInArray
函数
子 1
Sub Example(ws_string As String)
Sheets(ws_string).Activate
LR = Range("a1000").End(xlUp).Row
LC = Range("zz1").End(xlToLeft).Column
cName = "Fund ID"
cA = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
cName = "BBH ID"
cB = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
cName = "Description"
cC = ActiveSheet.Rows.Find(What:=UCase(cName), LookAt:=xlWhole, SearchDirection:=xlNext).Column
cName = "Security Type"
cD = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
cName = "Price Date"
cF = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
cName = "Current Price"
cG = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
cName = "Prior Price"
cH = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext).Column
子 2
Set aSelection = Range("C2:C1500")
Set aSelect_Recon = Sheets("Recon").Range("L2:C1500")
For Each cell In aSelection
If IsInRange(cell.Value, aSelect_Recon) Then
cell.Interior.ColorIndex = 10
End If
Next cell
Function IsInRange(stringToBeFound As String, ByVal rng As Range) As Boolean
Dim r As Range
Set r = rng.Find(What:=stringToBeFound, _
MatchCase:=True, _
LookIn:=xlValues, _
Lookat:=xlPart) 'partial match to have the same behaviour as the filter version
If Not r Is Nothing Then IsInRange = True
End Function
编辑更新此外,我三次调用Sub 1,通过不同的工作表,如下所示:
Example "ETF"
Example "MAV"
Example "Main"
但是,如果我注释掉 ETF 和 MAV 调用,则 Main 过程调用工作正常。
你需要Set
找到值,不能分配它们
Set rngTest = ActiveSheet.Rows.Find(What:=UCase(cName), Lookat:=xlWhole, SearchDirection:=xlNext)
if Not rngTest is Nothing then
cA= rngTest.Column
end if
建议:开始使用Option Explicit
,这将有助于避免此类错误。
我认为这就是问题所在:Find
方法的选项被保存。(它们与 excel 中的查找窗口中相同)。运行IsInRange
函数后,选项 MatchCase
设置为 True
。在您的示例 sub 中,您正在寻找UCase(cName)
因此您在IsInRange
后运行它时正在寻找"CURRENT PRICE"
,当找不到它时它会抛出Object not set
错误。
解决方案是在Example
子中设置MatchCase:=False
。
编辑:遵循 cyboashu 关于Option Explicit
的建议并首先检查对象,即使这现在解决了问题!
(PS:我在IsInRange
函数中包含MatchCase:=True
选项,以重现与Filter
函数相同的结果。
如果它解决了您的问题,也请接受答案(您甚至可以投票)。
如果它不能解决您的问题,请写评论并解释原因。