Using VBA to search over multiple sheets

[beardy]

Morning all,

I've banged my head against this problem for the last week and I'm sure I'm either inches or thousands of miles away from the solution

I have a workbook with multiple sheets and need to create a VBA script to search for one or more text strings across all the sheets - the answers may be in any of the columns on the data sheets.

I then need to return the answers (in the form of the contents of the first 4 cells in the row the string has been found in) to a different sheet within the same workbook (too many likely positive results for a message box or other userform based return system) and somewhere along the way remove any duplicate returns.

I can kinda bludgeon VBA into coughing up the whole row, but with duplicates..but can't get beyond that.

The additional complication is that the cell(s) that any 'find' function finds my search string(s) in may be in any column of the data.

I have been smart enough to make the first column of every sheet a unique key (unique to the sheet and the row on that sheet)...if that helps.

I'm running Excel 2003 (and VBA6).

Any help would be greatly appreciated - even just some good pointers for me to play with would be a good start lol

Thanks
Antony

 

[SteveO59L]

I think that you may need to be more specific about the range you want to search.

A worksheet has 256 columns x 65, 000 rows, more than 16, 000, 000 seperate cells, and thats without multiplying by n sheets.

 

[Joe Was]

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("Enter text to find")

If myText = "" Then Exit Sub

For Each ws In ThisWorkbook.Worksheets
With ws
'Do not search sheet4!
If ws.Name = "Sheet4" 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 sheet4 Option!
'Found.EntireRow.Copy _
'Destination:=Worksheets("Sheet4").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 "Found: """ & myText & """ " & foundNum & " times." & vbCr & _
AddressStr, vbOKOnly, myText & " found in these cells"
Else:

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

 

[beardy]

I think that you may need to be more specific about the range you want to search.

A worksheet has 256 columns x 65, 000 rows, more than 16, 000, 000 seperate cells, and thats without multiplying by n sheets.

The string(s) I'll be searching for will be somewhere in columns B to P. Rows-wise it'll be in (theoretically) any row apart from the first one (which has the column headers).

 

[beardy]

Joe Was - thanks for that...if I follow the logic of the VBA then I think that just might do it. You're a star
Of course if it doesn't work I'll be back scratching my head (but that'll be down to me not explaining myself properly - having gotten what I asked for, not what I wanted lol)

 

[Bill__]

Just read this topic with great interest.
In one of beardy’s posts he quotes, “The string(s) I'll be searching for will be somewhere in columns B to P. Rows-wise it'll be in (theoretically) any row apart from the first one (which has the column headers).”

My two questions are:
How can the macro be modified to search just those columns, or even narrow the range down further to something like cell B1 to cell P200 ?

Also, is it possible to modify the macro so it only searches the active sheet rather than all the sheets?

 

[Joe Was]

Sub findAllRangeDat()
'Sheet module code.
'Find my data in my Range and clear it!
Dim MyData, firstAddress, myBottom

On Error GoTo myEnd

MyData = "None"
myBottom = Sheets("Sheet1").Range("E65536").End(xlUp).Row

With Worksheets(1).Range("E1:E" & myBottom)

Set c = .Find(MyData, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do

c.Select
Selection.Value = ""

Set c = .FindNext(c)

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

myEnd:
End Sub

or

Sub findAllDat()
'Sheet module code.
'Find my data in my Range!
Dim Message, Title, Default, MyData, firstAddress

On Error GoTo myEnd

Message = "Enter what you are looking for, below:" ' Set prompt.
Title = "Find This Information!" ' Set title.
Default = "Add your info here!" ' Set default.

' Display message, title, and default value.
MyData = InputBox(Message, Title, Default)

With Worksheets(1).Range("a1:a500")

Set c = .Find(MyData, LookIn:=xlValues)

If Not c Is Nothing Then
firstAddress = c.Address
Do

c.Select
MsgBox Sheets("Sheet1").Cells(1, ActiveCell.Column).Value

'Do something with the find!
'Selection.ClearContents
'Selection.EntireRow.Delete

Set c = .FindNext(c)

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

myEnd:
End Sub

 

[Bill__]

Many thanks Joe for your reply. I've found these two VBA routines very useful indeed.

Regards -
Bill

 

[Gluv1983]

Hello, I really hope I can explain myself clearly. I am new to using VBA for Excel and have made some progress but I am really stuck now.
I am trying to create a workbook that is searchable across multiple sheets from one main screen, once the results are found I would like them to be listed on a results section of the main search page. By this I mean that I would like to embed a Search button on the opening page of the workbook (Im sure I could find out how I can do this myself) Using information from this site I can create a form that will call a module using a button that will begin the search function (this is what I would like to embed)
I have tried two different examples of code from this site that give me part of what I want to do. The first reply from Joe Was was close to what I am looking to achieve.
To make it exactly what I need I would need to be able to search up to 6 different words (of both letters and numbers mixed) from varying columns on every row row from all pages of the workbook.
These results would then be shown on the "Main Page" in a results section of the page. I assume this would be a range of cells that are blocked off for this function.
I couldn't read the forum rules as the link didn't seem to work for me, apologies in advance if I have posted incorrectly,
I appreciate your help in advance,
Best Regards,
Gary

 

[David12345]

This is great, thank you!
However the information reports a list of cells in a message box rather than all of the information going to a new sheet. Am I missing something?