时间戳

首页 > 解决方案 > 在没有循环的特定列中获取与搜索“字符串”匹配的单元格的行号 - 列有多个匹配项

excel - 在没有循环的特定列中获取与搜索“字符串”匹配的单元格的行号 - 列有多个匹配项

问题描述

在没有循环的特定列中获取与搜索“字符串”匹配的单元格的行号 - 列有多个匹配项”

我想在不循环的情况下获取特定列中匹配字符串的行号，因为我有超过 50000 条记录，我不想循环每一行来找出

Sub Mismatch()
    Dim sht As Worksheet

    Set Sht5 = ThisWorkbook.Worksheets("Result")

    Dim FindString As String
    FindString = "FAIL"    

    Sht5.Activate
    Columncount = Sht5.Range(Cells(1, 1), Cells(1, 1000)).Cells.SpecialCells(xlCellTypeConstants).Count 'CODE NEED TO BE UPDATED WITH COLUMN LENGTH
    'To find the column count

    lastReportRow = Sht5.Range("B" & Rows.Count).End(xlUp).row
    'to find the last used row

    For i = 2 To Columncount + 1
        Set Valuefound = Sht5.Range(Cells(2, i), Cells(lastReportRow, i)).Find(FindString, After:=Range("B2"), LookIn:=xlValues)

        If Valuefound Is Nothing Then
            MsgBox "Value not found"   
        Else
            For r = 2 To lastReportRow
                ActualString = Sht5.Cells(r, i).Value
                If FindString = ActualString Then
                    MsgBox r
                Else

                End If
                'For x = 2 To lastReportRow
            Next    
        End If
    Next
End Sub

标签： excelvba

解决方案

您可以使用匹配：

    '...
    lastReportRow = Sht5.Range("B" & Rows.Count).End(xlUp).row


    For i = 2 To Columncount + 1

        Set rng = Sht5.Range(Sht5.Cells(2, i), Sht5.Cells(lastReportRow, i))
        Do
            m = Application.Match(FindString, rng, 0)
            If IsError(m) Then Exit Do '<< not found: exit search for this column
            Debug.Print "Found '" & FindString & "' at " & rng.Cells(m).Address
            'reset search range
            Set rng = Sht5.Range(rng.Cells(m+1), Sht5.Cells(lastReportRow, i))
        Loop

     Next i
End Sub

推荐阅读