How to store sequential finds into different variables

Question

I have a sheet with a bunch of account information. I am looping through a range to locate accounts with the word "District" in the name. I want to store the range for each one I find. There will never be more than 4. So I thought I would set 4 range variables, loop through the list, and fill the variables, as shown here.

Sub AcctCalcs1()
    Dim sAcct1 As String, sAcct2 As String
    Dim rAcct1 As Range, rAcct2 As Range
    Dim wsAccts As Worksheet
    Dim iCtr As Integer, iAcct_Ctr As Integer

        Set wsAccts = ThisWorkbook.Sheets("Accounts")
        iCtr = 1
        iAcct_Ctr = 0
        Do Until iCtr = 21 Or IsEmpty(wsAccts.Range("B" & iCtr + 1)) Or iAcct_Ctr = 4
            If InStr(wsAccts.Range("B" & iCtr), "District") > 0 Then
                iAcct_Ctr = iAcct_Ctr + 1
                Select Case iAcct_Ctr
                    Case Is = 1
                        sAcct1 = wsAccts.Range("B" & iCtr).Text
                    Case Is = 2
                        sAcct2 = wsAccts.Range("B" & iCtr).Text
                   'Case Else....
                End Select
            End If
            iCtr = iCtr + 1
        Loop
    End Sub

I first thought the case statement idea would work. (only posting 2 instances for brevity) But as soon as I hit the 2nd find sAcct2, then sAcct1 address also changes because the increment counter has changed as well. I thought about Find and FindNext, but I believe it will be the same issue with the counter changing. I need to store the ranges because multiple sections of code will need to use them. Is there any way of storing these in variables without writing out to a sheet somewhere? I did see another post mentioning an array, but frankly mastering arrays is not something I have been able to do. Thanks for any advice.

Answer 1

Using a Collection for example:

Sub AcctCalcs1()
    
    Dim colAccts As Collection
    Dim wsAccts As Worksheet, c As Range, v
    
    Set wsAccts = ThisWorkbook.Sheets("Accounts")
    
    Set colAccts = New Collection
    Set c = wsAccts.Range("B2") 'start here
    Do While Len(c.Value) > 0
        v = Trim(c.Value)
        If InStr(v, "District") > 0 Then colAccts.Add v 'add to collection
        Set c = c.Offset(1) 'next row down
    Loop
    
    Debug.Print "Found " & colAccts.Count & " matches"
    For Each v In colAccts
        Debug.Print "Account: " & v
    Next
    
End Sub