Find All Occurrences of string in range
'The following code finds all occurrences of a string in a given range. Just call the following function with the required parameters - string to find and range to find in. It returns a range.
Function rngFindAll(strFindWhat As String, rngFindIn As Range) As Range Dim arrOrgData Dim strBlankChar As String On Error GoTo 0 Application.EnableEvents = False Application.Calculation = xlCalculationManual If rngFindIn.Cells.Count = 1 Then If strFindWhat = rngFindIn.Value Then Set rngFindAll = rngFindIn Else Set rngFindAll = Nothing End If GoTo ExitH End If strBlankChar = "A-A-AB" 'Can be anything that is unlikely to be found in the range arrOrgData = rngFindIn 'Save Range to array On Error Resume Next With rngFindIn .SpecialCells(xlCellTypeBlanks).Value = strBlankChar 'Change Blanks to other char (Temporarily) .Replace strFindWhat, vbNullString, xlWhole 'Replace the Find string with Blank If .Cells.Count = 1 Then If .Value = strFindWhat Then Set rngFindAll = rngFindIn End If Else Set rngFindAll = .SpecialCells(xlCellTypeBlanks) 'Find all blanks End If End With On Error GoTo 0 rngFindIn = arrOrgData 'Restore range from array On Error Resume Next Erase arrOrgData On Error GoTo 0 ExitH: Application.EnableEvents = True Application.Calculation = xlCalculationAutomatic End Function
| Attachment | Size |
|---|---|
| FindAllWithoutLoop.xls | 39.5 KB |
»
- Vishesh's blog
- Login or register to post comments
- 29097 reads
Usage example pls
An example of how to use it is much appreciated.
Example sheet attached
Dear Animas,
An example sheet has been attached for your reference.
Excel doesn't provide a way to find multiple occurrences of string in a range. This method is actually a workaround to achieve that without looping through the range. However, Excel does provide the facility to find multiple blanks in a range which has been used here as a trick. Hope you find it useful. Please note that no error handling has been used in the example code.