if anyone can help me.... I have a large date with many columns. I want to see the word I'm looking for in all the columns. I have a code but the message that comes out at the end is not very explanatory, I want it cleaner...
VBA Code:
Option Explicit
Private Sub Find()
Dim rngResult As Range
Dim strToFind As String
'Set to your desired string to find
strToFind = "Ireland"
'If the string you are searching for is located in
'the worksheet somewhere, you can set the value
'like this: strToFind = Worksheets("Sheet1").Range("A1").Value
'This assumes your search term is in cell A1 of the
'Sheet1 worksheet.
'Look in the used range of a given worksheet
'Change Sheet1 to match your worksheet name
With Worksheets("Sheet1").UsedRange
'Find the first cell that contains the search term
Set rngResult = .Find(What:=strToFind, LookAt:=xlPart)
'If it is found, grab the cell address of where the
'search term can be found
If Not rngResult Is Nothing Then
Dim firstAddress As String, result As String
firstAddress = rngResult.Address
'Loop through the rest of the cells until returning
'to the first cell that we had a match in.
Do
'Record the cell address of the match
'to the result string
result = result & rngResult.Address & ","
'Go to next cell containing the search term
Set rngResult = .FindNext(rngResult)
'Exit the loop when we reach the starting point
Loop While rngResult.Address <> firstAddress
'Output the list of cells that contain the string to find
MsgBox "Found """ & strToFind & """ in cell(s): " & result
End If
End With
End Sub
Last edited by a moderator:
