在VBA中编程多个.FindNext [英] Programming Multiple .FindNext in VBA

问题描述

我对VBA经验不足，但是通常可以完成一些简单的任务.我目前对.Find函数有问题.我知道excel没有能力执行两个.finds，但是我在编码第二个查找的循环时遇到了问题.到目前为止，我的代码在这里:

I am relatively inexperienced in VBA but can usually get simple tasks completed. I am currently having an issue with the .Find function. I know excel does not have the ability to do two .finds, but I am having problems coding a loop for the second find. The code I have so far is here:

Dim j As Integer

Dim Total As Integer
Total = Application.CountIf(Sheets("Output").Range("A:A"), "*Structure*")

Dim class As String
class = "CLASS ="

Dim str As String
str = "Structure" 

With Sheets("output")
Set rng1 = .Range("A:A").Find(str, lookat:=xlPart)
    row1 = rng1.Row
Set rng2 = .Range("A:A").FindNext(rng1)
    row2 = rng2.Row

            For j = 6 To Total + 5
                    If Application.WorksheetFunction.CountIf(Sheets("output").Range("A" & row1 & ":A" & row2), "*" & class & "*") > 0 Then
                        Set rng3 = .Range("A" & row1 & ":A" & row2).Find(class, lookat:=xlPart)
                        Sheets("sheet2").Cells(7, j).Value = Mid(rng3, 9, 3)         
                    Else
                        Sheets("sheet2").Cells(7, j).Value = ""
                    End If
                        row1 = row2
                        Set rng2 = .Range("A:A").FindNext(rng2)
                        row2 = rng2.Row
            Next j
End With

我有代码搜索"Structure"一词，以便为第二个.Rind创建一个Range，然后在另一个工作表上填写表格.我知道问题出在多个.Find上，但是找不到我能完全理解的任何帮助.

I have the code searching for the word "Structure" in order to create a Range for the second .Find and then fill out a table on a different worksheet. I know the issue is with the multiple .Find but could not find any help that I could fully understand.

推荐答案

您可能会发现将查找所有匹配项"部分抽象到一个单独的函数中比较容易.这将简化您的逻辑，并使实际任务更易于管理.

You may find it easier to abstract out the "find all matches" part into a separate function. That will simplify your logic, and make the real task easier to manage.

注意:这不会在最后一个"* structure *"单元格之后搜索"* CLASS = *"-尚不清楚是否需要这样做.

Note: this doesn't search for "*CLASS =*" after the last "*structure*" cell - it's not clear if you need to do that.

Sub Tester()

    Dim found As Collection, i As Long, f As Range, v

    With ActiveSheet

        'start by finding all of the "structure" cells...
        Set found = FindAll(.Range("A:A"), "*Structure*")

        'then loop over them...
        For i = 1 To found.Count - 1

            v = ""
            Set f = .Range("A" & found(i).Row & ":A" & _
                                 found(i + 1).Row).Find("*CLASS =*")

            If Not f Is Nothing Then v = Mid(f, 9, 3)
            Sheets("Sheet2").Cells(7, 5 + i).Value = v

        Next i

    End With

End Sub

FindAll函数:

Public Function FindAll(rng As Range, val As String) As Collection
    Dim rv As New Collection, f As Range
    Dim addr As String

    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

这篇关于在VBA中编程多个.FindNext的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！