Store "all found values" addresses list directly into Array

[cgcamal]

Hi everyone,

When we want to search a string within a complete sheet or range, we use:

1-) Ctrl+F-->Input "String" to search-->Click on "Search next" (Excel goes to position/address of "String")

or

2-) Ctrl+F-->Input "String" to search-->Click on "Search All" (Excel shows addresses list of all ocurrences of "String")

Considering only 2nd option ("Search All"), somebody knows with VBA, how to store directly in an Array
the addresses list of all ocurrences of a matched "String"?

I would like to avoid to do N times "Find next..." and "store current in array", "Find next..." and "store
current in array", "Find next..." and ..... "store current in array".

Thanks in advance for any help.

Regards.

 

[mikerickson]

In Excel 2011, there is no FindAll option (there is a ReplaceAll)

This will ask for a search term and find all occurrences of that term and create a range, foundRange, that contains only those cells with the search term.

As for the array of addresses,
foundRange.Address will return a comma delimited string of the cells (Areas) with the search term.

Split could be used to turn that into an array.

Sub FindAll()
    Dim rangeToSearch As Range
    Dim firstFoundAddress As String
    Dim foundRange As Range, foundCell As Range
    Dim searchTerm As String
    searchTerm = Application.InputBox("Search for?", Type:=2)
    If searchTerm = "False" Then Exit Sub: Rem canceled
    
    With Selection
    Set rangeToSearch = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    If rangeToSearch.Cells.Count = 1 Then Set rangeToSearch = ActiveSheet.UsedRange
    
    With rangeToSearch
        Set foundRange = .Find(what:=searchTerm, after:=.Cells(1, 1), LookIn:=xlValues, LookAt:=xlPart, _
                        SearchDirection:=xlNext, MatchCase:=False)
                        
        If Not foundRange Is Nothing Then
            firstFoundAddress = foundRange.Address
            Set foundCell = foundRange
            Do
                Set foundRange = Application.Union(foundRange, foundCell)
                Set foundCell = .FindNext(after:=foundCell)
            Loop Until foundCell.Address = firstFoundAddress
        End If
        
        MsgBox foundRange.Address(, , , True)
    End With
    
End Sub

 

[cgcamal]

Hi Mike,

Thanks for your reply, I'll try to learn from your solution and certainly I'll use it.

Split could be used to turn that into an array.

I've done it in that way, thanks. But for me, it works partially for these reasons:
1-) If active cell is empty (e.g E5) I receive run time error 91, "Object variable or with block doesn´t established"
2-) I'm not sure why with the little sample table below the value in A2 is not listed in the output if active cell=A1
3-) Not all values are stored in ascending order and if I change "SearchDirection:=xlPrevious" and "after:=.Cells(30, 1)",
I receive run time error 13, "Types don't match".

Excel Workbook
	A	B	C	D
1	car
2	car
3	car
4
5
6	car
7				car
8
9
10
11
12		car
13
14
15
16
17			car
Sheet1

In addition, time ago in some thread in this forum I saw that somebody used "Find" method to store all ocurrences into an array without loop,
I think was like that, I'm not sure if it was using "Find" and "Collections" or "Find" and "Areas". Simply I don't remember exactly how he
did it but was without a loop, maybe somebody knows a similar approach.

Thanks in advance.

Regards

 

[mikerickson]

1) The code above didn't handle the situation where Selection was outside the UsedRange, that is addressed below.

2) With A1 selected, I got "[Workbook1]Sheet1!$A$6,$D$7,$B$12,$C$17,$A$1:$A$3" from above.
A2 is included in the last Area. But it is a bit odd, so I corrected the first After argument below.

3) This doesn't depend on whether the data is sorted.

Sub FindAll2()
    Dim rangeToSearch As Range
    Dim firstFoundAddress As String
    Dim foundRange As Range, foundCell As Range
    Dim searchTerm As String
    searchTerm = Application.InputBox("Search for?", Type:=2, Default:="car")
    If searchTerm = "False" Then Exit Sub: Rem canceled
    
    Set rangeToSearch = Selection
    If rangeToSearch.Cells.Count = 1 Then Set rangeToSearch = ActiveSheet.UsedRange
    
    With rangeToSearch
        Set rangeToSearch = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
    
    With rangeToSearch
        Set foundRange = .Find(what:=searchTerm, after:=.Cells(.Rows.Count, .Columns.Count), LookIn:=xlValues, LookAt:=xlPart, _
                        SearchDirection:=xlNext, MatchCase:=False)
                        
        If Not foundRange Is Nothing Then
            firstFoundAddress = foundRange.Address
            Set foundCell = foundRange
            Do
                Set foundRange = Application.Union(foundRange, foundCell)
                Set foundCell = .FindNext(after:=foundCell)
            Loop Until foundCell.Address = firstFoundAddress
        End If
        
        Range("F4") = foundRange.Address(, , , True)
    End With
    
End Sub

 

[cgcamal]

Hi Mike,

It's strange why with the 2nd version still doesn't show address A2. I was testing, I tried doing xlPrevious, but the same result.

I tried For Each loop follow the use of similar logic that you have used (using foundRange) but the same result, A2 missing.

After that, I tried to test with a string variable within the same For Each Loop and finally works.

Sub FindAll3()
Dim rangeToSearch As Range, foundCell As Range
Dim searchTerm As String, foundRange As Range
   
    searchTerm = Application.InputBox("Search for?", Type:=2, Default:="car")
    
    If searchTerm = "False" Then Exit Sub: Rem canceled

    Set rangeToSearch = Selection
    If rangeToSearch.Cells.Count = 1 Then Set rangeToSearch = ActiveSheet.UsedRange
    
    With rangeToSearch
        Set rangeToSearch = Application.Intersect(.Cells, .Parent.UsedRange)
    End With
        
With rangeToSearch
    For Each foundCell In rangeToSearch
        If foundCell = searchTerm Then
            'Set foundRange = Application.Union(foundRange, foundCell)
            x = x & "," & foundCell.Address
        End If
    Next
End With

Range("F4") = x
End Sub

Thanks for your time and help.

Best regards

 

[Rick Rothstein]

Here is a FindAll function (not macro) that you may find useful. This function, which cannot be used as a UDF, will return a range consisting of all the cell that meet your search criteria (which you can then use directly in your code or obtain any of its parameter values, such as the Address for the range of cells. Here is the function along with its attendant enumeration object...

Enum LookAtConstants
  xlWholeCell = xlWhole
  xlPartCell = xlPart
End Enum
 
Function FindAll(FindWhat As String, Optional LookAt As LookAtConstants = xlWholeCell, Optional MatchCase As Boolean = False, Optional SearchAddress As String) As Range
  Dim LastRowPlusOne As Long, RowOffset As Long, ColOffset As Long, SearchRange As Range, CopyOfSearchRange As Range
  On Error Resume Next
  If Len(SearchAddress) = 0 Then
    Set SearchRange = Selection
  Else
    Set SearchRange = Range(SearchAddress)
    If SearchRange Is Nothing Then Exit Function
  End If
  LastRowPlusOne = Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlFormulas).Row + 1
  If SearchRange Is Nothing Then Set SearchRange = Selection
  RowOffset = LastRowPlusOne - SearchRange(1).Row
  ColOffset = SearchRange(1).Column - 1
  Set CopyOfSearchRange = Cells(LastRowPlusOne, 1).Resize(SearchRange.Rows.Count, SearchRange.Columns.Count)
  Application.ScreenUpdating = False
  With CopyOfSearchRange
    SearchRange.Copy .Cells(1)
    .Replace FindWhat, "=" & FindWhat, LookAt, , MatchCase
    Set FindAll = .SpecialCells(xlCellTypeFormulas).Offset(-RowOffset, ColOffset)
    .Clear
  End With
  Application.ScreenUpdating = True
End Function

This function has one required argument, the FindWhat which is obviously the text you wish to search for, and three optional argument... the LookAt argument which makes uses the Enun constants xlWholeCell and xlPartCell which controls whether the text being searched for must fill the whole cell or not (the default value is xlWholeCell)... the MatchCase argument which controls whether the text being searched for must match the letter casing exactly or not (the default is False meaning the search is case insensitive)... and the SearchAddress argument which is a string value representing the address of the contiguous cell range to be searched (the default value, if omitted, is the currectly selected range of cells). Here is an example call to this function using all the arguments...

' Relying on positional arrangement
Debug.Print FindAll("cut", xlWholeCell, False, "A1:C10").Address
 
' Using the named arguments for clarity
Debug.Print FindAll(FindWhat:="cut", LookAt:=xlWholeCell, MatchCase:=False, SearchAddress:="A1:C10").Address

 

[cgcamal]

Hi Rick,

Thanks for your reply.

Great function! I'll try it certainly. It will save me a lot of time I think.

Many thanks really.

Regards