Vector of matches VBA

问题:

I am currently looking for the following issue. I would like to find every position of a particular matches and store them into a vector called pos. In my example I would like to know every row in the range(E1:E500) where "SG" appears. Then I would like to loop through this vector.

I have tried the following code but it seems not to work. Can anyone help me?

Sub test()
    Set rangenew = Range("E1:E500")

    pos = Application.Match("SG", rangenew, False)

End Sub

the results should be something like

pos = (1,6,8,10)

Then I would like to loop through this vector to test conditions.

Thanks for your great help.

回答1:

As @GSerg explained in the comments, there is no built-in function for returning matched row numbers. Something like the below should do what you're after.

Public Sub getRows()

    Dim wb As Workbook, ws As Worksheet
    Dim checkData() As Variant, pos() As Long
    Dim i As Long
    Dim matchCount As Long

    Set wb = ThisWorkbook
    Set ws = wb.Sheets(1)

    checkData = ws.Range("E1:E500")

    For i = LBound(checkData, 1) To UBound(checkData, 1)

        If checkData(i, 1) = "SG" Then

            matchCount = matchCount + 1
            ReDim Preserve pos(1 To matchCount)
            pos(matchCount) = i

        End If

    Next i

End Sub