Monday, September 6, 2010

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"
'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

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 :)



Anonymous said...

Appreciate your discussing. Quite simple and straightforward to master. Well done!

Anonymous said...

Thank you for a fantastic article which helped me to along the way, I am thankful for your energy in researching and penning this web blog

Anonymous said...

One of the better content articles I've ever previously read through on this issue. Thanks!