using vba so search multiple Sheets

i'm trying to search across multiple sheets based on data submitted via an 
input box. So essentially, you click the button, a box appears, you type what 
your looking for and if it finds it, it'll select it otherwise a error 
message appeats.

i found the below code, but it only works if the cell with the value in it 
is active (i.e. i've clicked on it).

Code:
Dim datatoFind
Dim sheetCount As Integer
Dim counter As Integer
Dim currentSheet As Integer

On Error Resume Next
currentSheet = ActiveSheet.Index
datatoFind = InputBox("Please enter the value to search for")
If datatoFind = "" Then Exit Sub
sheetCount = ActiveWorkbook.Sheets.Count
If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
For counter = 1 To sheetCount
Sheets(counter).Activate
Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False).Activate
If ActiveCell.Value = datatoFind Then Exit For
Next counter
If ActiveCell.Value <> datatoFind Then
MsgBox ("Value not found")
Sheets(currentSheet).Activate
End If


thanks in advance :)
0
Utf
1/21/2010 7:12:02 PM
excel.programming 6508 articles. 2 followers. Follow

2 Replies
1489 Views

Similar Articles

[PageSpeed] 48

This code will scan each worksheet in the activeworkbook looking for the 
first instance of the text entered into the Input Box.  If the text isn't 
found in the entire workbook a message book appears telling the user it 
wasn't found.  Hope this helps!  If so, let me know, click "YES" below.

Option Explicit

Sub FindData()

Dim MyData As String
Dim wks As Worksheet
Dim rngFoundData As Range

    ' get users data
    MyData = InputBox("Please enter the value to search for.")
    If MyData = "" Then Exit Sub
    
    ' search all sheets in workbook
    For Each wks In Worksheets
        
        ' find data in current worksheet
        Set rngFoundData = wks.Cells.Find(What:=MyData, _
                                        LookIn:=xlFormulas, _
                                        LookAt:=xlWhole, _
                                        SearchOrder:=xlByRows, _
                                        SearchDirection:=xlNext, _
                                        MatchCase:=False)
        
        ' select found data and exit sub
        If Not rngFoundData Is Nothing Then
            wks.Activate
            rngFoundData.Select
            Exit Sub
        End If
        
    Next wks
    
    ' tell user data wasn't found
    If rngFoundData Is Nothing Then
        MsgBox MyData & " was not found in " & ActiveWorkbook.Name, 
vbInformation
    End If

End Sub
-- 
Cheers,
Ryan


"Lorcan Dene" wrote:

> i'm trying to search across multiple sheets based on data submitted via an 
> input box. So essentially, you click the button, a box appears, you type what 
> your looking for and if it finds it, it'll select it otherwise a error 
> message appeats.
> 
> i found the below code, but it only works if the cell with the value in it 
> is active (i.e. i've clicked on it).
> 
> Code:
> Dim datatoFind
> Dim sheetCount As Integer
> Dim counter As Integer
> Dim currentSheet As Integer
> 
> On Error Resume Next
> currentSheet = ActiveSheet.Index
> datatoFind = InputBox("Please enter the value to search for")
> If datatoFind = "" Then Exit Sub
> sheetCount = ActiveWorkbook.Sheets.Count
> If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
> For counter = 1 To sheetCount
> Sheets(counter).Activate
> Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
> :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
> False).Activate
> If ActiveCell.Value = datatoFind Then Exit For
> Next counter
> If ActiveCell.Value <> datatoFind Then
> MsgBox ("Value not found")
> Sheets(currentSheet).Activate
> End If
> 
> 
> thanks in advance :)
0
Utf
1/21/2010 8:04:06 PM
very much appreciated.

thanks!!


"Ryan H" wrote:

> This code will scan each worksheet in the activeworkbook looking for the 
> first instance of the text entered into the Input Box.  If the text isn't 
> found in the entire workbook a message book appears telling the user it 
> wasn't found.  Hope this helps!  If so, let me know, click "YES" below.
> 
> Option Explicit
> 
> Sub FindData()
> 
> Dim MyData As String
> Dim wks As Worksheet
> Dim rngFoundData As Range
> 
>     ' get users data
>     MyData = InputBox("Please enter the value to search for.")
>     If MyData = "" Then Exit Sub
>     
>     ' search all sheets in workbook
>     For Each wks In Worksheets
>         
>         ' find data in current worksheet
>         Set rngFoundData = wks.Cells.Find(What:=MyData, _
>                                         LookIn:=xlFormulas, _
>                                         LookAt:=xlWhole, _
>                                         SearchOrder:=xlByRows, _
>                                         SearchDirection:=xlNext, _
>                                         MatchCase:=False)
>         
>         ' select found data and exit sub
>         If Not rngFoundData Is Nothing Then
>             wks.Activate
>             rngFoundData.Select
>             Exit Sub
>         End If
>         
>     Next wks
>     
>     ' tell user data wasn't found
>     If rngFoundData Is Nothing Then
>         MsgBox MyData & " was not found in " & ActiveWorkbook.Name, 
> vbInformation
>     End If
> 
> End Sub
> -- 
> Cheers,
> Ryan
> 
> 
> "Lorcan Dene" wrote:
> 
> > i'm trying to search across multiple sheets based on data submitted via an 
> > input box. So essentially, you click the button, a box appears, you type what 
> > your looking for and if it finds it, it'll select it otherwise a error 
> > message appeats.
> > 
> > i found the below code, but it only works if the cell with the value in it 
> > is active (i.e. i've clicked on it).
> > 
> > Code:
> > Dim datatoFind
> > Dim sheetCount As Integer
> > Dim counter As Integer
> > Dim currentSheet As Integer
> > 
> > On Error Resume Next
> > currentSheet = ActiveSheet.Index
> > datatoFind = InputBox("Please enter the value to search for")
> > If datatoFind = "" Then Exit Sub
> > sheetCount = ActiveWorkbook.Sheets.Count
> > If IsError(CDbl(datatoFind)) = False Then datatoFind = CDbl(datatoFind)
> > For counter = 1 To sheetCount
> > Sheets(counter).Activate
> > Cells.Find(What:=datatoFind, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
> > :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
> > False).Activate
> > If ActiveCell.Value = datatoFind Then Exit For
> > Next counter
> > If ActiveCell.Value <> datatoFind Then
> > MsgBox ("Value not found")
> > Sheets(currentSheet).Activate
> > End If
> > 
> > 
> > thanks in advance :)
0
Utf
1/21/2010 11:14:01 PM
Reply:

Similar Artilces:

Special Pasting a work book with many sheets and formulas
I have a workbook with many sheets that all have formulas and links to other data. I want to save the workbook as another name with all the worksheets keeping the values only (no links or formulas). Is there a quick way to do this for everysheet without having to special paste every sheet in the workbook. So can I save everysheets data values at workbook level. See this page for a code example http://www.rondebruin.nl/values.htm -- Regards Ron de Bruin http://www.rondebruin.nl/tips.htm "lex63" <lex63@discussions.microsoft.com> wrote in message news:ED708...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Excel and some disappeared sheets
Anybody has the same or similar experiences : - I worked with excel file / closed as usually - new day opened and unfortunatelly some very important sheets disappeared / why ? Don=B4t know - I had about 7-9 sheets just now only 3 ones are there but not so important as another ones - the file has original size / I quickly looked into file by normal text editor only for reading and all relevant data are there, however if I open file by Excel, NO original sheets or data are there. - this file is VERY IMPORTANT for me - why happened ? Don=B4t know since I ask anybody for help Thanks in advan...

Can SUMPRODUCT be used for entire column?
This formula results in a numeric result: =SUMPRODUCT((A1:A20)*(B1:B20="b")) but this formula results in a #NUM! result: =SUMPRODUCT((A:A)*(B:B="b")) Which means I need to specify the length of the columns, which may grow over time. Any way to do this for the entire column, without having to specify the length of the column? XL07 removed the limitation on array formulas (which SUMPRODUCT is, even though it doesn't require CTRL-SHIFT-ENTER) and entire columns. For pre-XL07, one can use =SUMPRODUCT(A1:A65535, --(B1:B65536="b")) to get all but on...

multiple email identities
HELP! My husband and I share the same computer and we have our email come to our inbox through Microsoft Outlook. However it is merging our accounts- how do we seperate into 2 identities so we each get our own mail! It is so frustrating! Please help! Thanks Kat Outlook does not use Identities - it uses profiles. See this page for more information: http://www.slipstick.com/outlook/share.htm --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After s...

Using scanner in Word97
I want to scan a picture into word97 using my HP 4370 ScanJet. Could not find option of "From Scanner" under "Insert" --> "Picture". Apparently I must need some sort of Word97 Add-On. What and where is the add-on? Is it on the Office97 CD? Same applys to Excel97. Also, does microsoft sponsor a Word97 / Office97 discussion group? If so, would appreciate a link. "PSRumbagh" <PSRumbagh@discussions.microsoft.com> said this in news item news:39100DC1-A7EE-4679-881D-526BAA386620@microsoft.com... > I want to scan a picture ...

Multiple domains on one Exchange server / All but one domain shows up as spam #2
I am sorry if this is on the board somewhere but I have been searching forever and havn't found a hint yet. I have multiple domains setup on my exchange server. Lets say abc.com, def.com, and hij.com. We only send email with @abc.com but can receive from any domain name. Recently we merged with another company and we brought back their email to our server, lets say xyz.com. Now I configured a new recipient on Exchange 2003, so I now have two policies. The first policy has the three original domains in it with @abc.com as the primary domain. In the second policy I have configured xyz.com t...

Using later version of microsoft access
Hi, I've got access 2000 on my computer. When I go to open a database someone sent me I get an error message : this database is in an unrecognized format. The database may have been created with a later version of microsoft access. Is there any way I can open and use this file (short of upgrading to later version of access)? If you do not have Access 2002 or 2003, ask the person to save it in Access 2000 format for you. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at m...

Using Company Wide Mail Templates.
Hi, I do not know if i am at the right spot here, or if it is evne possible, but i got the following question. My boss would like me to make sure that every outgoing mail has the same looks. It starts by adding a signature that is the same for everyone, except with ofcourse personalized information. This was easily done by giving everyone a signature. The next question is however, to put the head of our website, also above our mail. This means that every user that sends a mail, the mail will have a nice header, underneath that header, the mail is typed, and then its ended with the si...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

how create Quota filter in WIndows 2003 R2 using Script
Hi all I need create quota filter in Server 2003 R2 using vbscript. quota filter should be applied to directories and not by users. I searched information about it on google without success. thank's in advance Daniel Hi Daniel. First you need open your FSRM (File Server Resource Manager), then you create a quota template, you must specify if your quota is "software" (just monitoring, but never deny the user) or "hardaware" (deny users when they use 100% of the quota), you must specify if you want send e-mail to user when this user use...

Opening Multiple Web Links in a Column
Hi, I am very new to using web links in excel. A task I do very often is open a list of different websites that are in various columns in an excel spreadsheet. I am quite sure I am doing it the slowest way possible, so I need some help please. Basically I have been clicking on one link at a time. When I do this, the first website opens and excel automatically minimizes, then I have to go re maximize excel and then click the next web link and the same thing happens, etc... very time consuming. I am wondering if there is a way, either through Excel or whatever means necessary, to open all...

how can I do a lookup into another sheet?
how can I do a lookup into another sheet? thanks ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~View and post usenet messages directly from http://www.ExcelForum.com/ Try something like =VLOOKUP(123,Sheet2!A1:B10,2,FALSE) -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "melawaisi" <melawaisi.ws67m@excelforum-nospam.com> wrote in message news:melawaisi.ws67m@excelforum-nospam.com... > > how can I do a lookup into another sheet? > thanks > > > ------...

Using Publisher 200 with Publisher 2003
How do I covert PUB2000 documents to Pub2003 documents and vice versa? Pub 2003 can open anything, no conversion necessary. Going backward is a bit trickier. File - Save As and chance the file type to a Pub 2000 file. Possible problems can arise if you've used a feature that was not available in the 2000 version and your file size will grew immensely. -- JoAnn Paules MVP Microsoft [Publisher] "nasuco" <nasuco@discussions.microsoft.com> wrote in message news:500C7A7A-4026-434C-8CC2-2DFDB69D81C4@microsoft.com... > How do I covert PUB2000 documents to Pub2003 do...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

VBA form
Im using a form/macro that i linked to through another thread and a tailoring it to my needs. I basically have it doing what i need but cant get a button to do what i want. When the user starts the macro i brings up the form with a message and 2 buttons (Continue/Cancel). Whe continue is pressed i want the message on the form to change to aniothe message and so on until all messages are shown. Ive attacehed what i working on and apprecaite any help offered. Thank yo +------------------------------------------------------------------- |Filename: Excel.zip ...

searching a cell for a contained text word
Is it possible to search a cell for a key word or words contained in text made of multiple words enabling the user to than create a pivot table using the collected key word or words as data? Doug K ...

creating template for multiple emails
How or can one create an email template (same subject line and message)for multiple emails (approx 100's)? Needing to shorten the time it takes sending 100's of individual emails (with the same subject line and same message) to sales staff on a weekly (some times daily) basis. Company does not want sales force using other sales persons email addresses (they are contractors). Try using a Distriburtion List, send one message, it goes to all of them, or get the message ready, put your address in the TO: field, and put everyone else in the BCC: field?? That way no one can see wher...

calendar for multiple time zone users: all day events
Users in different time zones post absences and meetings to a shared company calendar. When an all day event is scheduled in one time zone, it shows as spanning two days for other time zone users. How do we make this work properly? For the others it does intrude on a second day. BossLady wrote: > Users in different time zones post absences and meetings to a shared company > calendar. When an all day event is scheduled in one time zone, it shows as > spanning two days for other time zone users. How do we make this work > properly? Until you upgrade to Outlook...

Seperate jobs for multiple worksheets
Separate jobs are sent to printers when I select multiple worksheets... If I print to PDF, multiple files are created. How can I avoid this? ...

Speeing up searches in CRM
When our call agents take a call and then search for a student by name or ID it is taking about 10-15 seconds to return the search results. Is there a way to improve the search response time? I'm wondering if there is a config setting in CRM, or are we talking additional indexes on the actual SQL tables, any ideas?!??! Thanks. To speed up searches you will have to add indexes to the database. There are white papers available on this subject (crm 4.0 performance) http://www.microsoft.com/downloads/details.aspx?FamilyID=ba826cee-eddf-4d6e-842d-27fd654ed893&DisplayLang=en http://...

How to use different return email addresses
I use outlook 98. I have a mailbox with three aliases. People sending me emails can use any of the three email aliases and the email will arrive in the same mailbox. Accessing the mailbox once will retrieve all messages regardless of which alias was used. If I reply to these emails though, I want the recipient to think that the email has come from the alias that they originally used. What outlook always seems to do is use the email address of the service that is listed first in Tools->Services->Delivery. Is there any way Outlook can be set up so when I reply to a given email, my email ...

How do I assign a name to a PDF report via VBA
Thanks to the internet site: http://msdn.microsoft.com/en-us/library/ee336132.aspx I have the following subprocedure: Private Sub Print_to_PDF_Click() On Error Resume Next Dim reportName As String reportName = "HCBS CMgr Smmry Report" DoCmd.OpenReport reportName, _ View:=acPreview, WindowMode:=acHidden Set Reports(reportName).Printer = _ Application.Printers("CutePDF Printer") DoCmd.OpenReport reportName, _ View:=acViewNormal End Sub This appears to be a good start; however, it stops and waits for me to enter the n...

vba vs VB dotnet
I have a new project to use MS access as backend. I would like to know what adre the difference betwwen VBA and VB dotnet as front end? what is the best choise and what I need considre to use front end bwteen 2 choices? Your information is great appreciated, Souris wrote: > I have a new project to use MS access as backend. > > I would like to know what adre the difference betwwen VBA and VB > dotnet as front end? > > > what is the best choise and what I need considre to use front end > bwteen 2 choices? Well a big difference is that you can actually create a fr...