CODEWITHSUNDEEP
vbaloopsvectormatch
richpiana
richpiana

Reputation: 421

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.

Upvotes: 1

Views: 52

Answers (1)

luke_t
luke_t

Reputation: 2985

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

Upvotes: 1

Related Questions