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

 

[Ebaad]

Hi Joe,

With the following code working behind a userform, I was able to get the values around the found text and was able to post it in a text box back in the serch form. But what I actually want is to paste the values into a list box in columns first and from there the users should be able to select a record to edit and open the details in another edit form.

I was able to do this successfully in access by using the subform but the powers to be want me rewrite the whole thing in Excel. So I guess that is what I'm stuck with. I have been reading your code all day and trying out several things and it has been working great but now I have reached a point where I need your help.

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

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

'myText = InputBox("Enter text to find")

If txtSearch = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
    With ws
        Set Found = ws.Range("C1:C5000").Find(what:=txtSearch, LookAt:=xlWhole, MatchCase:=False)
        If Not Found Is Nothing Then
            FirstAddress = Found.Address
            Do
                foundNum = foundNum + 1
                AddressStr = AddressStr & .Name & " " & Found.Address & " " & Found.Cells.Offset(0, -2).Value & " "
                AddressStr = AddressStr & Found.Cells.Offset(0, -1).Value & vbCrLf
                Set Found = ws.Range("C1:C5000").FindNext(Found)

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

If Len(AddressStr) Then
    MsgBox "Found: """ & txtSearch & """ " & foundNum & " times." & vbCr & _
    AddressStr, vbOKOnly, txtSearch & " found in these cells"
    txtResult = AddressStr
Else:
    MsgBox "Unable to find " & txtSearch & " in this workbook.", vbExclamation
End If

End Sub

I would really appreciate any help in this regards,

Ebaad.

 

[Joe Was]

You need to add some code in the search loop to add the found items one at a time to the ListBox. The process works like:


For Each c In Range("Range1")
ListBox1.AddItem (c.Value)
Next c

So, you can add something like:

ListBox1.AddItem (Found.Address)

 

[MPBJR]

I have been working with your code below, and let me say it works great..nice work! I was wondering if it could be tweaked a little to have it re open the input box after it finishes a search to perform another search for a new number? Thanks again!
Mike

 NEWSEARCH Macro
' Macro recorded 8/29/2007 by Mike Breen
'
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

myText = InputBox("    ENTER JOB NUMBER 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

myFind = MsgBox("Found one """ & myText & """ here!" & vbCr & vbCr & _
thisLoc, vbInformation + vbOKCancel + vbDefaultButton1, "Your Result!")

If myFind = 2 Then Exit Sub

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 " JOB # " & myText & " NOT FOUND. ", vbExclamation
End If

End Sub

 

[MPBJR]

sorry..did not mean to leave my name in the code..and in no way am I trying to take credit for your work...sorry! I just pasted your code in a old macro and for got to erase my name.

 

[Joe Was]

That has been added before in this post, check above.

 

[JCFarx]

Hey Joe love your searh VBA, I am trying to modify it to work with the Calendar Control 10.0 so a user could choose a date then click search button to launch in to your VBA (bypassing the text entry). My main problem is understanding how I could modify the myText variable to accept the variable generated by the calendar control. No need to go to the trouble of doing any code for me I am wanting to learn and am really just looking for some bread crumbs to follow, my code is such a mess right now it would be useless to post for you to review lol. Thanks in advance.

 

[Joe Was]

Sorry, had the flu, also the Board is being hit by search bots, big time, like Yahoo and Google, they have just about brought the board to a stand-still. In any case, this is an easy way.

Here I add the Calendar to a UserForm and add a TextBox to return the selected data for that date, to that UserForm's TextBox. The data is offset to the cell to the right of the key date, hence the code to pull the data to the right of the selected date.

Private Sub Calendar1_Click()
'UserForm Calendar, Day Click.
Dim myRange As Range
Dim myNote As String
Dim myDate As Date

'Hold selected date.
myDate = Calendar1.Value

Worksheets("Calendar").Range("AA:AB").Activate
Set myRange = Worksheets("Calendar").Range("AA2:AB4")

'Lookup Date-Note.
With Worksheets("Calendar").Range("AA:AB")
Set c = .Find(myDate, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address

Do
myNote = .Find(myDate, LookIn:=xlValues).Offset(0, 1).Value
TextBox1 = "On: " & myDate & ", " & myNote
Worksheets("Calendar").Range("A1").Select
Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress

'When date not found!
Else

Worksheets("Calendar").Range("A1").Select
TextBox1 = "On: " & myDate & ", No Note Was Found!"
End If

End With
End Sub

 

[ejs7597]

I am using the below code, to search for text the user enters, and then I made the code also clear the cell, when it finds the text. It works for me, but it does more than I need it to, and want to make it a little more fool proof. Instead of searching for the text in all sheets, I only want it to search in the active sheet, and only in column A. Does anyone know how to edit this code to make it work that way. I have been trying without any luck.
Thanks

Sub myfind()
Dim Message, Title, Default, SearchString
Message = "Enter your search string!" ' Set prompt.
Title = "Find ? On all sheets!" ' Set title.
Default = "" ' Set default.
' Display message, title, and default value.
SearchString = InputBox(Message, Title, Default)

'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S
End Sub

 

[Joe Was]

Wrong code to do that!
The "S" is the Sheet loop and you need to remove it and add the ActiveSheet Object.


This is not needed:
'SearchString = "Rob"
Set S = Sheets.Application
For Each S In Application.Sheets
With S.Range("A1:IV65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
S.Select
Range(Location).Select
Exit For
End If
End With
Next S

Try this:

With ActiveSheet.Range("A1:A65536")
Set F = .Find(SearchString, MatchCase:=True, LookAt:=xlWhole, LookIn:=xlValues)
If F Is Nothing Then
Else
Location = F.Address
ActiveSheet.Select
Range(Location).Select
Exit For
End If
End With

 

[MPBJR]

I've been using the code below for several months with no problems. Now I have created another workbook that I need to use the same code for. I copied the code over to the new workbook, which has several sheets to search, but after it finds the first value, it won't search a 2nd sheet and I keep getting an error 91 massage.

When I hit the debug button it takes me to this line in the code :

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

I'm not sure why this is doing this, any ideas??

Sub JOBSEARCH()
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