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

 

[Joe Was]

Yes, just add that qualifyer:

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)


Becomes:

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

By default "LookAt" is Whole or xlWhole!


P. S. The code works for me too, so I do not know what the problem can be!

 

[faxo]

Hi,

In relation to searching multiple workbooks I had a similar post:

http://www.mrexcel.com/board2/viewtopic.php?p=1080919#1080919

This might be of use.

Fax.

 

[grd]

Yes, just add that qualifyer:

Set Found = .UsedRange.Find(what:=myText, LookIn:=xlValues, MatchCase:=False)


Becomes:

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

By default "LookAt" is Whole or xlWhole!


P. S. The code works for me too, so I do not know what the problem can be!

Excellent - now works a treat.

Thanks very much Joe Was

Graham

 

[pap]

Re: Run time error 91

Hi All - Isle of Man calling

I no nothing about VB at all and I am having the same problem as John

Run time error 91

Object variable or with block variable not set

Can anyone please assist or point me in the right direction?

I thank you in advance for any assistance.

Regards
Paul

 

[Joe Was]

Many have asked for this code to be modified, so I do not know which version you are using?

Post the code!

Also, this code will only work from a Standard code module, like: Module1 and not a sheet module, like: Sheet1!

To add a Standard module to your workbook, Alt+F11, VBA ToolBar: Insert - Module, then paste a copy of the posted code to that module. Delete any other version using the same name!

 

[itsme2]

Search Mulitple Sheets

I have changed Sheets("rngNm").Select to ActiveSheet.Select it works fine however I have two questions regarding this...

1) How do "imbed" this search box into a spreadsheet so that I do not need to run the macro to see the search box, and

2) How can I retrieve the results and place them in cell(s) right below the above...

It's really nice to see that it is found and where however, can I get this to display the results of say the entire row from which the search string was found?

Thanks

 

[Joe Was]

1). Change "myText" in the code to a cell reference rather than an InPutBox!

myText = ActiveSheet.Range("A1").Value

2). Just put the Found Value into a reference derived from the "End(xlUp)" structure.

Say you want the list of found rows to be on Sheet2:

Found.EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

 

[itsme2]

Oh thanks... I understand how to incorporate/change the first line of code referencing to a cell range however, I really do not understand where to place the second line of code....

Thanks

 

[Joe Was]

Public Sub FindTextFromCell()
'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

myText = Sheets("Sheet2").Range("A1").Value

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

If .Name = "Sheet2" Then GoTo myNext
If .Name <> "Sheet2" Then _
Found.EntireRow.Copy _
Destination:=Worksheets("Sheet2").Range("A65536").End(xlUp).Offset(1, 0)

Set Found = .UsedRange.FindNext(Found)

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

myNext:
Next ws
End Sub

 

[itsme2]

Find Text

The above works wonders however, how can I DIM myText so that if more then 1 match is found allow only the next match to be copied to the sheet,,,

I guess what I am say is if I search for "A" I am getting 100's of responses can't this be limit to two at the most copy those and a msgbox display like "Please refine your search more than 2 instances have been found"

THanks