redirect msgbox output

colinharwood

Active Member
Joined
Jul 27, 2002
Messages
397
Office Version
  1. 2019
Platform
  1. Windows
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

zacemmel

Well-known Member
Joined
Apr 29, 2002
Messages
636
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

Active Member
Joined
Aug 21, 2002
Messages
255
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

Active Member
Joined
Jul 27, 2002
Messages
397
Office Version
  1. 2019
Platform
  1. Windows
Could you be more specific, with my code

Thanks

Colin
 

Forum statistics

Threads
1,144,358
Messages
5,723,891
Members
422,524
Latest member
wirkkarn

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top