Find text

[colinharwood]

Hi All

I have a workbook which contains 12 sheets, is it possible write a macro which will search for a text string which could be on any of the 12 sheets

Thanks a lot

colin

 

[comanche23]

Hi Joe, I was amaze with your work.

Please also help me in adding an option that when I click ok after all the text has find. It will prompt me again if I want to do a Search again. If selected YES will go back to "What text do you want to find?"

Also is it possible to add "Find All" button (in the workbook) and will give the text in the cell instead of (Sheet1 $A$10) will be (Sheet1 Word). Just want to eliminate the strings $.

Also please show me how to add a seperate button (command button) to link this macro. So when i finish searching and would like to Search again I can just click the Command Button and will do the same thing


Thanks and i am really new with this so I aoplogize.

 

[Joe Was]

The first is:

An easy fix, you add a message box with a OK Cancel or Yes No button option then test for which button the user clicked then use "GoTo" to re-direct the code flow back to the begining using a "Tag."

On the page before this I worte the code:

myF = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel, "Found!")

'MsgBox myF

If myF = 2 Then GoTo myEnd

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 "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

myEnd:
End Sub

Just change the text in the MsgBox to something like: "Would you like to search again?"

Change the Tag "myEnd:" to some other name, like: "myAgain:" and move it to the Top of the code!


The third is:

From the toolbar: View - Toolbars, select Forms. Left click the Button Icon from the displayed toolbox. Drag - Size your new button into about the right location and shape on your sheet, the button utility will automatically ask you for the macro name to attach to the button.

Right click the rear of the button caption lable, it may ask you to select edit, and "Backspace" the default caption out. Right click the button and select "Format Control..." select Font: Font style: Bold then Color: Dark Red then OK. Add your new button caption lable.

You are done. You can resize or move the button position with a right click. Edit change the font size, whatever.

The control button is slower, but has more options. The control button also may require modified code to run and is harder to use than "Form Buttons." All code will run from the "Form Button" above, but may not on a control button, new users should use Form buttons and stay away from control buttons, unless you have better than average VBA skills.

You can also use "Auto Shapes" and right click your macro-code to that shape much the same way as the Form button! The auto shape will act just like a Form Button! You can also import a photo and use it as a button.

The second is?
Starts out leading me to think you want to also return the found cell's value, but then you say: "Just want to eliminate the strings $." And, you lost me?

 

[comanche23]

Thanks Joe.

Could you please give me the completed macro:

Here is what I am using. Thanks.

Public Sub FindText1()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range, rngNm As String
Dim myText As String, FirstAddress As String, thisLoc As String
Dim AddressStr As String, foundNum As Integer
Dim myF As Variant

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
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address

Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select

myF = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel, "Found!")

'MsgBox myF

If myF = 2 Then GoTo myEnd

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 "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:
MsgBox "Unable to find " & myText & " in this workbook.", vbExclamation
End If

myEnd:
End Sub


By the way on my Second question I just want to appear it like the name of the found text and not the Cell# or value.

 

[Joe Was]

Public Sub FindText1()
'Run from standard module, like: Module1.

Dim ws As Worksheet, Found As Range
Dim rngNm As String, myText As String, FirstAddress As String
Dim AddressStr As String, thisLoc As String
Dim foundNum As Long
Dim myF As Variant, myRD As Variant

myAgain:
myText = ""
FirstAddress = ""
foundNum = 0
rngNm = ""
AddressStr = ""
thisLoc = ""
myF = ""
myRD = ""

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
foundNum = foundNum + 1
rngNm = .Name
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
thisLoc = rngNm & " " & Found.Address

Sheets(rngNm).Select
Range(Found.Address(RowAbsolute:=False, _
ColumnAbsolute:=False)).Select

myF = MsgBox("Found one """ & myText & """ here!" & vbLf & vbLf & _
thisLoc, vbInformation + vbOKCancel, "Found!")

If myF = 2 Then GoTo myQuit

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 "Found: """ & myText & """ " & foundNum & " times." & vbLf & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else

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

myEnd:
myRD = MsgBox("Search Again?", vbInformation + vbOKCancel, "Re-Run?")

If myRD = 1 Then GoTo myAgain

myQuit:
End Sub

 

[JS]

Hi!

First of all... great forum. It helped me a lot in my struggled excel moments.

Can someone help me with the following thing? I'm trying to make a search function in my workbook using a script from this board (see below). I made some minor alterations to and it works great. If I use this script it shows the cell locations of the results as it shoud be.

Is it possible to show the actual data form the top cell from the collumn where the data was found?

For example... If the search result is cell C12 i want the msgbox to show the data crom cell C1. All cells from the first row are filled with titles.

Here is the code I'm using. There are some Dutch words... please ignore them. :wink:

Public Sub FindText()
'Run from standard module, like: Module1.
'Find all data on all sheets!
'Do not search the sheet the found data is copied to!
'List a message box with all the found data addresses, as well!

Dim ws As Worksheet, Found As Range
Dim myText As String, FirstAddress As String
Dim AddressStr As String, foundNum As Integer

myText = InputBox("Geef een artikelnummer of een gedeelte van een artikelnummer op.", "Zoeken")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws

'Do not search Profieloverzicht!
If ws.Name = "Profieloverzicht" Then GoTo myNext

'Do not search Profielen!
If ws.Name = "Profielen" Then GoTo myNext

'Do not search Algemeen!
If ws.Name = "Algemeen" Then GoTo myNext

'Do not search Verwerkers!
If ws.Name = "Verwerkers" Then GoTo myNext

Set Found = .UsedRange.Find(What:=myText, LookIn:=xlValues, LookAt:=xlPart, MatchCase:=False)

If Not Found Is Nothing Then
FirstAddress = Found.Address
Do
foundNum = foundNum + 1
AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf
Set Found = .UsedRange.FindNext(Found)

'Copy found data row to Profieloverzicht Option!
'Found.EntireRow.Copy _
'Destination:=Worksheets("Profieloverzicht").Range("A65536").End(xlUp).Offset(1, 0)

Loop While Not Found Is Nothing And Found.Address <> FirstAddress
End If

myNext:
End With
Next ws

If Len(AddressStr) Then
MsgBox "Gevonden profielen voor " & myText & vbCr & _
AddressStr, vbOKOnly, myText & ""
Else:
MsgBox "Geen resultaten gevonden voor " & myText & "", vbExclamation
End If

End Sub

I've searched the board many many times using a variety en combination of words, but I didn't find any results covering my problem.

Thanks in advance for help.

 

[Joe Was]

Change this line:


AddressStr = AddressStr & .Name & " " & Found.Address & vbCrLf


To:

AddressStr = AddressStr & .Cells(1, .Range(Found.Address).Column).Value & ": " & .Name & " " & Found.Address & vbCrLf

The key is:

.Cells(1, .Range(Found.Address).Column).Value

This will return the Column Label, if the label is in row one, for the column that the data was found in. Changing this line only changes the summery message box at the end!

 

[JS]

Thanks Joe, I will check it tomorrow first thing in the morning when I'm at work. Thanks in advance.

 

[JS]

It works great. Thanks.

One more question. Is it possible to change the cell-address in the results into the actual data found in that cell?

AddressStr = AddressStr & .Cells(1, .Range(Found.Address).Column).Value & ": " & .Name & " " & Found.Address & vbCrLf

The bold part in the code refers to the cell-address where my data is found. I'm doing a partial word search and it would be great that the message box shows the full data found in that cell instead of the cell-address.

The message box will show this... Column Label: Full Cell Data (if it's possible)

 

[JS]

Nevermind... I've found the solution.

 

[Joe Was]

It should just be:

Found.Value