Instead of Msgbox display results in a column

Hello everybody,

First, love Excel forums, have helped me a lot in the last 2 years. Love you!

Now i have a question where i cannot find an answer:

I found a Macro in the Internet which helps me: It displays all cells that have a given value in it.

BUT it displays me the result in a Msgbox!

i would like to have it in Sheetxyz in column A.

Here is the formula:

Sub WBSearch()
Dim ws As Worksheet
theWord = InputBox("Enter text to search", "Search Criteria")
If theWord = "" Then
Exit Sub
End If
For Each ws In ActiveWorkbook.Worksheets
ws.Select
For Each cell In ActiveSheet.UsedRange
If InStr(1, UCase(cell.Value), UCase(theWord)) Then
wIndex = wIndex & Chr(10) & ActiveSheet.Name & " - " & _
cell.Address(0, 0)
End If
Next
Next
If wIndex = "" Then
MsgBox UCase(theWord) & " was not located in this workbook.", _
vbOKOnly, "Not Found"
Else
MsgBox wIndex, vbOKOnly, UCase(theWord) & " was found in:"
End If
End Sub


Thanks a lot in advance

Please put your code in

Please Login or Register  to view this content.

Welcome to the Forum. Please review the Forum Rules when posting and use code tags for your code.

http://www.excelforum.com/forum-rule...rum-rules.html

Try:

Please Login or Register  to view this content.

Thank you John and Thank you TMS

Although i have a small issue with your formulas, thank you very much for giving me such a quick answer!

John, when I try your macro, VBA marks me the second "Next" at the end and writes "Next without For". I have no VBA knowledge, so no clou.

TMS, your formula works fine, there is just 2 small thing i would like to change:

1)Is it possible to display each result in a single cell instead of all results in one cell?
2) Is it possible not to dsiplay the actual cells, but their content. If for example i search for "Lauren" it doesnt tell me "Cell A2" but gives me "Laurenzino.

Thanlk you very much!!!!

Laurenzino

My mistake delete one of the Next's.

Then it tells me:

"Block If without End if"

This will output the cell value instead of the cell address, as requested.

Please Login or Register  to view this content.

Thanks a lot Wallrus egg man and everybody else!