find text in workbook

August 24, 2009 at 06:00:30
Specs: win xp 5.1.2600, 3mhz/2 gig
Hi, I want to type something into cell A17 on worksheet 'MASTER'
Then I click a button below it and I want excel to automatically open the page the found item is on and highlight the cell.
Example: I type 199/456 into cell A17 on the worksheet called MASTER.
199/456 will appear on a worksheet called HANDBOOK SHEETS.
I want the cell 199/456 to be selected.
Is this possible? Is it too much work to do? I had a look in the findnext help and I couldnt get it to work. Could I not use something like below?
Thank you
Private Sub CommandButton1_Click()
With Application.FindNext(worksheet.master.range("a17"))
.LookIn = "active.workbook"
.Range ("a1:b500")
End With
End Sub

See More: find text in workbook

Report •

August 24, 2009 at 07:53:15
Try something like probably don't need all of the .Find arguments, but they won't hurt.

Sub ActivateSearchCell()
 For Each sht In ActiveWorkbook.Sheets
  If sht.Name = "Master" Then GoTo nxtSht
   With Sheets(sht.Name)
    Set c = Sheets(sht.Name).Cells.Find(What:=Worksheets("Master").Range("A17"), _
         After:=ActiveCell, LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, _
         SearchDirection:=xlNext, MatchCase:=False, SearchFormat:=False)
     If Not c Is Nothing Then
       Exit Sub
     End If
   End With
End Sub

Report •

August 25, 2009 at 05:21:11
Hi DerbyDad03,
Thank you for your reply. I have put it into my spreadsheet.

The line ***Range(c.Address).Select***
always comes up with an error. I dont understand why.
Can you make sense of this? The error is:
Run-time error '1004':
Select method of Range class failed


Report •

August 25, 2009 at 05:48:49
Where did you put the code - in a regular module or in a sheet module?

I pretty sure it works as written when placed in a regular module, but might fail if in a sheet module, depending on which sheet the value is found in.

In either case try changing the line to be:


Let me know if that helps.

Report •

Related Solutions

August 25, 2009 at 06:15:03
Thank you DerbyDad. I used your new line and it works!
I dont know how to put it in a module, so it is in my sheet.
That seems to work for me.
If I put it in a module (is that like a macro?) then when I click my button do I just call the module?

Cheers, and thanks for your time writing that script for me.

Report •

August 25, 2009 at 06:46:54
VBA has various modules that can contain code, and as we've seen, macros can act differently depending on where they are placed.

Typically a macro of this type, which is accessing all sheets in the workbook, would be placed in what is typically called a "regular" or "standard" module. Some macros must be placed in a Sheet module and some must be placed in the ThisWorkbook module in order to function correctly.

To add a regular module to your workbook, open the VBA editor, click on Insert...Module and a Module1 window should open.

Another option is to create a personal.xls file and put your macros in modules in that workbook.

If you store a file called personal.xls in the XLStart directory (e.g. C:\Program Files\Microsoft Office\Office\XLStart) Excel will open that file every time you start the application and all macros stored in that file will be available for use in any workbook.

If within personal.xls you choose Window...Hide and then save it in XLStart, it will open "hidden" and you won't see it, but the macros will be available.

Report •

August 25, 2009 at 06:59:13
Wow you have opened up a new window for learning about Excel + VB. Thanks for the info and I will test out the different ways of storing macros.
I tested it by creating a new module and that works. Thanks again!!!

Report •

Ask Question