1. Please post your adjusted code. See my signature block for how to use Code tags to preserve the indentations in the code as it makes it much easier to read & debug.I adjusted the code you gave me to look for G1 in stead of "abc" but that's the only change i made.
Sub FindAllInstances() Dim ws As Worksheet
Dim FirstAddress As String, msg As String
Dim Found As Range
For Each ws In Worksheets
If ws.Name <> "Search" Then
FirstAddress = vbNullString
With ws.UsedRange
Set Found = .Find(What:=G1, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
msg = msg & vbLf & ws.Name & "-" & Found.Address(0, 0)
Set Found = .FindNext(After:=Found)
Loop Until Found.Address = FirstAddress
End If
End With
End If
Next ws
If Len(msg) Then
MsgBox msg
Else
MsgBox "Search item not found"
End If
End Sub
OK, that explains why you were getting a whole sheet full of meaningless answers. The code you have written is looking for the contents of a variable called G1. Since you haven't said what value that variable is holding, it will be looking through your whole workbook for blank cells. Naturally it will find a LOT!!!It's the contents of cell G1 on the "Search" sheet that i'm looking for
Option Explicit
Sub FindAllInstances()
Dim ws As Worksheet
Dim FirstAddress As String, msg As String, sProd As String
Dim Found As Range
sProd = Sheets("Search").Range("G1").Value
For Each ws In Worksheets
If ws.Name <> "Search" Then
FirstAddress = vbNullString
With ws.UsedRange
Set Found = .Find(What:=sProd, LookAt:=xlWhole, _
MatchCase:=False, SearchFormat:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
msg = msg & vbLf & ws.Name & "-" & Found.Address(0, 0)
Set Found = .FindNext(After:=Found)
Loop Until Found.Address = FirstAddress
End If
End With
End If
Next ws
If Len(msg) Then
MsgBox msg
Else
MsgBox "Search item not found"
End If
End Sub
I don't understand. That sounds to me like if you were looking for "abc" you want "abc" listed?is there a way to list the info itself rather than just the cell reference.
1. What other columns?.. the macro finds the "G1" info in the E column, is there a way to list the info from other cells on the same row?