EXCEL VBA Store search results in an array?

Question

As the title say is it possible and how?

I have found a .Find function to search a column for the values I want, is it then possible to save all the addresses in an array?

The code looks like this:

Set wsRaw = Worksheets("raw_list")
Set oRange = wsRaw.Columns(PhaseCol)

SearchString = "control"

Set aCell = oRange.Find(What:=SearchString, LookIn:=xlValues, _
            LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
            MatchCase:=False, SearchFormat:=False)

If Not aCell Is Nothing Then
    Set bCell = aCell
    FoundAt = aCell.Address
    Do While ExitLoop = False
        Set aCell = oRange.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            FoundAt = FoundAt & ", " & aCell.Address
        Else
            ExitLoop = True
        End If
    Loop
Else
    MsgBox SearchString & " not Found"
End If

MsgBox "The Search String has been found these locations: " & FoundAt
Exit Sub

As for now I have a MsgBox just to show the results. The idea was to store the result in an array if possible.

score 0 · Accepted Answer · answered Jul 03 '12 at 13:46

0

Yes you can do that. See this example

Dim MyResults() As String
Dim n As Long

n = 1

'
'~~> rest of the code
'

If Not aCell Is Nothing Then
    Set bCell = aCell

    ReDim Preserve MyResults(n)
    MyResults(n) = aCell.Address
    n = n + 1

    Do While ExitLoop = False
        Set aCell = oRange.FindNext(After:=aCell)

        If Not aCell Is Nothing Then
            If aCell.Address = bCell.Address Then Exit Do
            ReDim Preserve MyResults(n)
            MyResults(n) = aCell.Address
            n = n + 1
        Else
            ExitLoop = True
        End If
    Loop
Else
    MsgBox SearchString & " not Found"
End If

You can then later loop through the array to show the results

For i = LBound(MyResults) To UBound(MyResults)
    Debug.Print MyResults(i)
Next i

answered Jul 03 '12 at 13:46

Siddharth Rout

147,039
17
206
250

Thank you so much for the fast reply! – Andreas Jul 03 '12 at 13:49
1

It was easy, considering the fact that you picked up the code from my blog :D – Siddharth Rout Jul 03 '12 at 13:50
**Compile error: expected-array** – bottledatthesource Dec 01 '21 at 02:15
@bottledatthesource: Just to let you know that the code that I post in stackoverflow is most of the time TRIED AND TESTED and if it is not then I specifically mention it :) I am sure you are doing something incorrectly. – Siddharth Rout Dec 01 '21 at 15:11
@siddhart how did you run it? I put it as a Function in VBA but can't get it to work. Do you send it an array? The code looks interesting. I would like to see how it works. I'm new also. – bottledatthesource Dec 01 '21 at 18:55
@bottledatthesource Then why downvote and not simply ask your query :)? – Siddharth Rout Dec 01 '21 at 19:28

score 0 · Answer 2 · answered Oct 13 '21 at 13:51

this code is used to update cells for indirect reference to other sheets, it finds cells with specific string, and update these cells value. (the fsstring stores the address of first find address, used for stop condition).

Sub Update_Sheet_Reference()
Dim sCell As Variant, fsCell As Variant
sString = "_SearchText"
sCell = Range("A1").Address

While True
sCell = Cells.Find(What:=sString, After:=Range(sCell), LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Address
        
Range(sCell).Value = Replace(Left(Range(sCell).Value, Len(Range(sCell).Value) - 1), sString, "") + sString + "!"
If fsCell = "" Then
    fsCell = sCell
ElseIf sCell = fsCell Then Exit Sub
End If
Wend
End Sub

EXCEL VBA Store search results in an array?

2 Answers2

Linked