Excel VBA : Find data using Control+F and not looping

Hi All,

I was sitting and reading few posts and saw that lots of people tries to loop and find the data in different ranges. But it really becomes very very slow if the data you are looping at is huge. Also, what if probability of the occurrences of the desired string/value is very low.

Lets take an example, if I am using approx 1 Million cells in a worksheet, and I am trying to find if a particular text "xysqhqhsyqhsn" is available in how many cells. Obviously, the probability is quite low, and even equal to nil that I will find this string at all in the whole worksheet. But to find, I will need to search first to ensure that the value doesn't exist in the worksheet or not. Now, which option which one would like to have, either to loop to all the 1 million cells or just to jump on the cells which contains the value?

Obviously, in out case, we would like for the second option. I always do that. I always go for the second option to search the string in a worksheet, rather than looping through all the cells, a slow movement.

I have shown an example here. In this example, first I take an input from the user, and tries to give a counter which signifies that the input string exists in how many cells. I have used a function "GetValidRanges" which would return a collection of cells, as shown below

Sub FindInstances()
    Dim inputStr As String ' the variable to accept the input from user
    Dim rngToFind As Range ' the range where we have to find
    Dim activeSht As Worksheet
    Dim cell As Range
    Dim firstAddress As String
    Dim txtCounter As String
    Dim ranges As Collection
    
    'take the input of the string which we want to search.
    
    inputStr = InputBox("Please enter the string which you want to search.")
    Set activeSht = ActiveSheet
    Set rngToFind = activeSht.Range("A1:A300")
    If (inputStr = "") Then
        MsgBox "Invalid Input"
    Else
        'there is a string to search
        Set ranges = GetValidRanges(inputStr, rngToFind)
        
        MsgBox "Total number of occurrences are " & ranges.Count
        
    End If
    
    
    
    
End Sub


'''this function loops through the cells in the range passed, and returns a collection of those cells (eventually range objects) which contains the passed values.

Public Function GetValidRanges(inputStr As String, rngToSearch As Range) As Collection
    Dim cell As Range
    Dim firstAddress As String
    Dim ranges As Collection
    
    Set ranges = New Collection
    
    With rngToSearch
        Set cell = .Find(inputStr, LookIn:=xlValues)
        
        If Not cell Is Nothing Then
            firstAddress = cell.Address
            
            Do
                ranges.Add cell
                Set cell = .FindNext(cell)
            Loop While Not cell Is Nothing And cell.Address <> firstAddress
        End If
    End With
    
    Set GetValidRanges = ranges
    
End Function

User can easily use this collection in many ways. We can get the count of the total cells returned, or loop through all the items in the collection to get the actual address.

Hope this helps :)

Thanks,
Vikas