redirect msgbox output

[colinharwood]

Hi All

I use the following code to search a workbook with multiple sheets to find required text>

Sub FindText()

Dim ws As Worksheet
Dim Found As Range
Dim myText As String
Dim FirstAddress As String
Dim AddressStr As String

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws

If Len(AddressStr) Then
MsgBox AddressStr, vbOKOnly, myText & " was found in these cells"

Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

End Sub

Is it possible to have the results of the search displayed on a worksheet named "Found", instead of in a msgbox

Thanks

Colin

 

[zacemmel]

Instead of using msgbox, make it equal to a cell value. For example, instead of using msgbox "hi", do this instead, range("a1").value="hi"

http://www.excelquestions.com
This message was edited by zacemmel on 2002-10-18 18:25

 

[rikrak]

Create a sheet named "Found" first and use this code to list the results.

<pre>
Sub FindText()

Dim ws As Worksheet
Dim Found As Range
Dim myText As String
Dim FirstAddress As String
Dim numFound As Long

myText = InputBox("Enter text to find")

If myText = "" Then Exit Sub

Sheets("Found").Range("A:A").Clear
numFound = 1
For Each ws In ThisWorkbook.Worksheets
With ws
Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)
If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
Sheets("Found").Cells(numFound, 1) = .Name & " " & Found.Address
numFound = numFound + 1
Set Found = .UsedRange.FindNext(Found)
Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If
End With
Next ws

If numFound = 1 Then Sheets("Found").Cells(numFound, 1) = myText & " not found"
End Sub
</pre>

 

[colinharwood]

Could you be more specific, with my code

Thanks

Colin

 

[colinharwood]

Thanks for that Rikrak

Colin