Easy VB command for selecting a cell

  Craig.m 20:09 19 Feb 2007

I am using Excel 2003, creating a spreadsheet that contains 100s of entries. There is one title entry then a laid out block containing various bits of information.

After filling in all the data I wanted a quick and easy way to get to the data I wanted - autofilter sounded good but once filtered it only gave the one line - the data being spread over nine, so lost the other eight.

I can make a list in a drop down box (on a front page)to put an entry in a cell but I would then like a button that will select information in the cell, go to column A on the second page and search for that entry then select the cell on the same row but in column E.

I can put VB in and tweak it to suit but am useless when it comes to making it up, it sounds simple but I would not have a clue how to set about doing it. Anybody that can help please

  silverous 20:12 19 Feb 2007

I know I could help if I could 100% understand what you are after... if autofilter only gives you one line it means you filtered on something that returns only one line ?

If you want to send/give an example I'd be happy to help. Others including VoG may understand what you are after without further explanation - maybe I'm being dim!

  VoG II 20:13 19 Feb 2007

You don't need a macro for this, you can use VLOOKUP.

On the first sheet enter the following formula where A1 contains what you are looking for

=VLOOKUP($A1, 'Sheet2'!$A$1:$E$2000, 5, FALSE)

change the sheet name and the number of rows to suit.

  silverous 20:35 19 Feb 2007

VoG - I don't think he wants to return a single value - that's why autofilter isn't helping - I think he wants to go to the data within the 2nd sheet.

It is clearer now i've re-read it. I'll have a play....

  Craig.m 20:45 19 Feb 2007

Silverous - that is correct, select a value from a list that will then take you to the column E cell adjacent to that entry on sheet 2. It is something that should make it easy for the user to navigate to that area more quickly - I hope

  silverous 20:45 19 Feb 2007

OK, got it. I'm assuming you know how to add a button and just need the code behind it.

Assuming your value you are looking for is in cell A1 on Sheet1 and your data is on sheet2, the following code behind a button should do it.

Of course you then have the problem of what if there is more than one matching value in Column A.

Private Sub CommandButton1_Click()

Dim oCellFound As Range

Set oCellFound = Sheets(2).Columns(1).Find(Sheets(1).Range("A1").Value)
oCellFound.Offset(0, 4).Select

End Sub

Let me know if you want me to send you the working example.

  Craig.m 20:53 19 Feb 2007

Thanks for that, will give it a try, though can you not specify an exact match in the line? There are some places that will have a place then the ere may be other versions of that - eg Bromwich and West Bromwich, as an incorrect example but gives the idea.

  VoG II 21:02 19 Feb 2007

VLOOKUP will do this - there is no need for any code!

  silverous 21:05 19 Feb 2007

Not sure what you mean.

With my example I think if you search for West Bromwich it won't find Bromwich. But if you search for Bromwich it will find both (but only the first one).

If you want it to only find exact matches put the following line in instead:

Set oCellFound = Sheets(2).Columns(1).Find(Sheets(1).Range("A1").Value, lookAt:=xlWhole)

  silverous 21:06 19 Feb 2007

VoG - no it won't! Vlookup will return a cell from sheet 2. It won't take the user to a row where, for example, they might want to make amendments.

He's looking for the kind of functionality you get in a database when you let a user search for a record.

  Craig.m 21:06 19 Feb 2007

Thanks for that, it appears to work the way I wanted it to.

Sometimes I get some VB and it all makes sense and then VoG or whisperer can give me something that is really simple looking and I cannot seem to fathom how it actually does what it does - tis a black art.

Thanks again, will have to try read up on simple (very simple) VB.


This thread is now locked and can not be replied to.

Elsewhere on IDG sites

Xiaomi Mi Mix 2 review

See mcbess's iconic style animated for Mercedes-Benz

iPhone X news: Release date, price, new features & specs

Black Friday 2017 : date, sites participants & bonnes affaires