Excel VBA -在表列中查找给定值的多个行号

hfyxw5xn  于 2022-12-14  发布在  其他
关注(0)|答案(1)|浏览(340)

我真的是VBA新手,我正试图在工作簿的两列中找回我有“假”的行号。
下面的代码在一定程度上起作用了,因为它只在第一个“FALSE”值处停止,并且在消息框中报告它,而我知道在其余列中有多个“FASE”值。我如何在消息框中报告所有具有“FALSE”值的行?

Dim CurrentWB As Workbook
 Set CurrentWB = ActiveWorkbook
 With ActiveWorkbook.Sheets("Sheet1")
    Set FindRow = .Range("J:J, K:K").Find(What:="FALSE", LookIn:=xlValues)
    If Not FindRow Is Nothing Then
        MsgBox ("FALSE found in row:" & FindRow.Row)
    Else
       MsgBox ("No FALSE found")
    End If
lxkprmvk

lxkprmvk1#

虽然对您/用户来说,在中包含“FALSE”行的数组上使用筛选器会更容易,但以下是您要查找的内容:

Sub testFind()
    Dim dict As Object: Set dict = CreateObject("Scripting.Dictionary")
    Dim rngF As Range
    Dim CurrentWB As Workbook
    Dim startFind As Long
    Dim startStr As String
    
    Set CurrentWB = ActiveWorkbook
    With ActiveWorkbook.Sheets("Sheet1")
        Set FindRow = .Range("J:J, K:K").Find(What:="FALSE", LookIn:=xlValues)
        If Not FindRow Is Nothing Then
            startFind = FindRow.Row 'to get out of the infinite loop
            startStr = "FALSE found in row:"
            Do Until FindRow Is Nothing
                If Not dict.Exists(FindRow.Row) Then 'in case there's multiple "FALSE" in the row
                    dict.Add FindRow.Row, FindRow.Row 'add row to dictionary in case it's the first "FALSE"
                    startStr = startStr & FindRow.Row & ", " 'add row to your message
                End If
                Set FindRow = .FindNext(FindRow) 'next find
                If FindRow.Row = startFind Then: Set FindRow = Nothing 'we're back to start so we can exit the loop
                startStr = Left(startStr, Len(startStr) - 2) 'get rid of the last ", "
            Loop
            MsgBox startStr
        End If
    End With
End Sub

这是未经测试的,但我确实从我自己的一个潜艇上抓到了它,并根据你的规格修改了它。希望这对你来说现在更容易理解:)

相关问题