Hello,
Does anyone know a way of being able to search ALL the sheets in one go on a workbook? Going through all the sheets maybe one by one, i cant fathem it at all.
Rich
Hello,
Does anyone know a way of being able to search ALL the sheets in one go on a workbook? Going through all the sheets maybe one by one, i cant fathem it at all.
Rich
Re: Searching
Note: Excel 97 doesn't provide the option of searching the entire workbook, but you can search all worksheets in a workbook by grouping them. You do this by right-clicking any Sheet Name tab and choosing Select All Sheets. In later versions of Excel, Find… and Replace… provide the option of searching within the sheet or workbook.
Re: Searching
Hi, thanks
is there a VBA code for selecting all sheets, as when i record the macro, it puts in the sheet names, where as each week there will be an additional sheet.
Re: Searching
Try this
[VBA]Sub SelectSheets1()
Dim mySheet As Object
For Each mySheet In Sheets
With mySheet
If .Visible = True Then .Select Replace:=False
End With
Next mySheet
End Sub[/VBA]
Note that mySheet is defined as an Object data type, instead of a Worksheet data type. This is done because in testing I encountered a problem with Chart sheets--they wouldn't be selected because they weren't of a Worksheet type.
Re: Searching
Quote from richardcranneyHi, thanks
is there a VBA code for selecting all sheets, as when i record the macro, it puts in the sheet names, where as each week there will be an additional sheet.
OR
If you have Excel 2000+ you can search all Worksheets as standard.
Re: Searching
Hello,
Thanks for that, ive had a go at doing the sheet, but wondered if anyone could help as im getting an error all the time.
If you search for Hello, it comes up with a error, where as if Hello was on sheet1, it would find it all the time.
Re: Searching
Hi, just to add, its runtime error 91 that it comes up with, dont know if this will help, coz im stumped!
Thanks again for all your help
Re: Searching
Hi there,
Sorry to bring this up again, just its baffling me, i dont understand it and was hoping somebody can help.
Ive tried various different pieces of code, but they all produce the same error, changing the .value to .text etc etc.
Ive had a search on the forum, but cant find any people trying to do the same thing as me, hope someone can help.
Thanks again
Rich
Re: Searching
Rich - I'm mad busy at work today... but at a first glance... you need to be careful...
If the result of the "cells.find" doesnt find anything, then you can not activate it.... if you know what I mean. You cant activate something that you doesnt exist.... :confused:
Anyway, change your code like so -
Sub findbutton_Click()
' Select all the Sheets in the workbook
Dim My_result As Variant
Worksheets.Select
' Now Search for the thing in the searchbox
Set My_result = Selection.Find(After:=ActiveCell, What:=Searchbox.SearchQuery.Text)
End Sub
Display More
Now... that will get rid of the error, but I dont have time to investigate what it is youre actually trying to do and further investigate if your approach above will get you there.... my search code in other post you saw might help you out too....
Ger
Re: Searching
Thanks for the reply, but using this it doesnt perform the search, not to what i can find out anyway.
Does anyone have any ideas on how to search all sheets for a keyword?
Its being used on Excel 97 as thats the only version we have at work due to the licence. I just can work out how to get a search to search one sheets, if its not there, change the sheet and search that one, until it finds the query.
Any help would be much appriciated.
Re: Searching
Groups the Worksheets you want to search. That is, hold down Ctrl and select the needed sheets.
Re: Searching
Quote from Dave HawleyGroups the Worksheets you want to search. That is, hold down Ctrl and select the needed sheets.
Thanks dave, but ive tried that and it doenst work to the full effect. Office 97 doesnt have a "search All", you can select multiple sheets and then do find but it doesnt find it. Ill have to look at another way of doing it via the vba code
Re: Searching
Is this better?
Sub findbutton_Click()
' Select all the Sheets in the workbook
Dim ws As Worksheet
Dim cl As Range
Dim sQuery As String
sQuery = Me.searchquery.Value
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
ws.Select
With ws.UsedRange
' Now Search for the thing in the searchbox
Set cl = .Find(sQuery, LookIn:=xlValues)
If Not cl Is Nothing Then
cl.Select
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
Display More
Re: Searching
Quote from royUKIs this better?
Code Display MoreSub findbutton_Click() ' Select all the Sheets in the workbook Dim ws As Worksheet Dim cl As Range Dim sQuery As String sQuery = Me.searchquery.Value Application.ScreenUpdating = False For Each ws In ActiveWorkbook.Worksheets ws.Select With ws.UsedRange ' Now Search for the thing in the searchbox Set cl = .Find(sQuery, LookIn:=xlValues) If Not cl Is Nothing Then cl.Select End If End With Next ws Application.ScreenUpdating = True End Sub
Thanks Roy for your input, nice to hear from you!
Ive tried inputting the code you mentioned above, but it seems to only search the last page.
On the sheet, as I had "hello" on the last sheet, i added a blank sheet to the end. Now when I run the search it just selects the last sheet. The code you have used it familiar to me, but to be honest, gone over my head a bit so dont know what would need changing. Ive added the workbook to see if you could help further.
Thanks again
Re: Searching
Try this alteration to Roy's code.
Sub findbutton_Click()
' Select all the Sheets in the workbook
Dim ws As Worksheet
Dim cl As Range
Dim sQuery As String
Dim FirstAddr As String
sQuery = Searchbox.searchquery.Value
Application.ScreenUpdating = False
For Each ws In ActiveWorkbook.Worksheets
With ws.UsedRange
' Now Search for the thing in the searchbox
Set cl = .Find(sQuery, LookIn:=xlValues)
If Not cl Is Nothing Then
cl.Interior.ColorIndex = 3
FirstAddr = cl.Address
Do
Set cl = .FindNext(cl)
cl.Interior.ColorIndex = 3
Loop Until cl.Address = FirstAddr
End If
End With
Next ws
Application.ScreenUpdating = True
End Sub
Display More
Re: Searching
Norie, thanks
Im getting to be a novice now, cant fathem it out. Is there a way of everytime it finds a result to select it on that sheet?
ive used the following:
[code]
If MsgBox("Is this the right result", vbYesNo Or vbQuestion) = vbNo Then
Carry on searching
Else
End
Endif
[code]
What im wanting to do is show the result before it comes up with the message box and just selects the cell that it is in rather than highlighting them all.
Re: Searching
Richard
I don't quite understand what you mean.:)
What would be the 'right result'?
Re: Searching
Hiya Norrie,
"hello" would be the right result in this case as it is the only thing on the sheets. but basically whatever is typed in the search box in the userform is whats being searched for.
If this is found, in this case "hello", rather than highlighting all the boxes, im needing it to select the box, take the user to that specific sheet and that specific cell.
Ive introduced the message box so that when the sheet and cell is selected, if thats not the right result, it will go to the next one.
Thanks again norrie, its much appriciated.
Re: Searching
Rich,
Change your find button code as follows:
Sub findbutton_Click()
' Select all the Sheets in the workbook
Dim ws As Worksheet
Dim c As Variant
Dim firstaddress As Variant
For Each ws In ThisWorkbook.Worksheets
' Now Search for the thing in the searchbox
Set c = ws.Cells.Find(What:=searchquery.Text, After:=[A1])
If Not c Is Nothing Then
firstaddress = c.Address
Do
c.Interior.ColorIndex = 6 'do whatever you want to do here.
Set c = ws.Cells.FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstaddress
End If
Next ws
End Sub
Display More
This code will find all instances on all sheets in one swoop - And color in yellow any cell that is found, as per commented line.
Note, you need to tightend up your code on the "END" button. It doesnt work right now.
Ger
edit: sorry guys - didnt realise there was a second page with this post.... not sure if my code above is of any use to anyone at this stage.
Don’t have an account yet? Register yourself now and be a part of our community!