excel - 嵌套 IF 语句后循环不再起作用

Question

我对 vba 编码非常陌生，因为昨天才开始学习它，我对我在这里做错了什么有疑问。

我正在尝试编写一段代码，在某个范围内查找某个单词，检查相邻单元格上的值，在另一个范围内查找这些值，如果找不到，则执行一些操作，然后返回到寻找下一个匹配的第一个范围。

在插入第二个 .find 之前，我设法使循环正常工作，但是一旦添加它，它就会破坏循环吗？它给了我“运行时错误 91:未设置对象变量或 block 变量”。

我尝试从 Range 更改变量的数据类型至String和来自 String至Range

我已经尝试过Set variable =只是variable =对于变量

我已经发布了第二个.Find()到达循环之前的变量

我移动了很多行

再说一遍，我对此很陌生，如果有人可以帮助我，我将非常感激

有问题的行是 Loop While cred.Address <> firstcred它在第一个子例程中工作正常，但在第二个子例程中返回错误

sub subname1()

Dim credeb As Range
Dim cred As Range
Dim firstcred As String
Dim nome1 As Range
Dim data1 As Range

Set credeb = Range("credeb")   
Set cred = credeb.find("crédito")

If cred Is Nothing Then
    GoTo Final

Else
    firstcred = cred.Address

    Do
        cred.Activate

        Set nome1 = cred.Offset(0, -2) 
        Set data1 = cred.Offset(0, -1) 

        nome1.Interior.Color = vbYellow
        data1.Interior.Color = vbYellow

        Set cred = credeb.FindNext(cred)

    Loop While cred.Address <> firstcred

End If

Final:
End Sub

______________________

Sub subname2()

Dim credeb As Range
Dim cred As Range
Dim firstcred As String
Dim nome1 As Range
Dim data1 As Range
Dim nome2 As Range
Dim nome11 As Range

Set credeb = Range("credeb")   'nome da tabela credito ou debito
Set cred = credeb.find("crédito")
Set nome2 = Range("nome2")     'nome da tabela de nomes do credito

If cred Is Nothing Then
    GoTo Final

Else
    firstcred = cred.Address

    Do
        cred.Activate

        Set nome1 = cred.Offset(0, -2)  
        Set data1 = cred.Offset(0, -1)   
        Set nome11 = nome2.find(nome1)


        If nome11 Is Nothing Then
            nome1.Interior.Color = vbYellow
            data1.Interior.Color = vbYellow

            Set nome1 = Nothing
            Set data1 = Nothing
            Set nome11 = Nothing

        End If

        Set cred = credeb.FindNext(cred)

    Loop While cred.Address <> firstcred

End If
Final:
End Sub

以上代码返回运行时错误“91”:未设置对象变量或 With block 变量。如何修复此代码以使其正常执行？

Answer 1

在循环中运行 Find 可能会使主代码难以维护，如果您需要在多个位置执行此操作，那么最好的方法是将其分解为单独的函数。

例如。你可以这样做:

sub subname1()

    Dim hits as Collection, hit 

    Set hits = FindAll(Range("credeb"), "crédito")

    if hits.Count > 0 then
        for each hit in hits
            hit.Offset(0, -2).Resize(1,2).Interior.Color = vbYellow
        next hit
    end if

End Sub

查找所有匹配项的函数:

'return all cells in "rng" which match "val", as a Collection
'  (edit the Find method parameters as needed)
Public Function FindAll(rng As Range, val As String) As Collection

    Dim rv As New Collection, f As Range
    Dim addr As String

    'best to be explicit about exactly what you want Find to do...
    Set f = rng.Find(what:=val, after:=rng.Cells(rng.Cells.Count), _
        LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
        SearchDirection:=xlNext, MatchCase:=False)
    If Not f Is Nothing Then addr = f.Address()

    Do Until f Is Nothing
        rv.Add f
        Set f = rng.FindNext(after:=f)
        If f.Address() = addr Then Exit Do
    Loop

    Set FindAll = rv
End Function