Lookup in multiplesheets

[Daniel Kvisten]

Hi,

I have searched in these pages for a solution for days, so now i post
my own message.
I have a workbook witch contains several worksheets, all users of
these workbook have access to page 1. On this page I try to make it
possible to type a spesific number and push a commandbuttom to get to
the place for all information about this account.

The challenge is that the information is spread over more than one
sheet, allthough all information about the account is in one page.
What I can't figure out is how to send the user to just this account
so that he or she may add new information to the account.

Help is severly needed!

Regards

 

[Guest]

Here's a simple example

Private Sub CommandButton1_Click(
Dim strSearch As Strin
strSearch = Worksheets("Sheet1").Range("A1").Valu

Dim sht As Workshee
Dim rngFound As Rang
For Each sht In Worksheet
Set rngFound = sht.UsedRange.Find(strSearch, sht.Cells.SpecialCells(xlCellTypeLastCell)
If Not rngFound Is Nothing The
sht.Activat
rngFound.Selec
Exit Su
End I
Nex
MsgBox "Value not found.
Worksheets("Sheet1").Range("A1").Selec
End Su

-Brad

 

[Tom Ogilvy]

http://tinyurl.com/2le6j

should give you plenty of examples.

 

[Daniel Kvisten]

Thanks Brad!

While testing your solution it occured to me that i should have been
more accurate in my questioning:

Sheet1 have a number, 5 digits, in cell e15

Sheet 2 have many accounts, roughly 2500.
And sheet 3 and 4 are similar to sheet 2.

With the number in cell e15 i wish the command button to find the right
account, select it and let the user alter values of information in the
account.

Each account have contains many sorts of information, the only
information not to be altered is the accountnumber.

Is this a better spesification?

Regards Daniel