Link to home
Start Free TrialLog in
Avatar of qeng

asked on

Excel 2010: VBA Script to go to a row in a Table based on a cell value

Using:  Excel 2010

Given a Table like this but with hundreds of rows:

ID#     Title
001     Title1
004     Title4
005     Title5a
005     Title5b
017     Title17

I would like a user to be able to enter an ID# in a cell (this becomes the lookup value), say "4", and for an adjacent 'GoTo' button, when double-clicked, to move the cursor to the cell in the Table which contains the first occurrence of ID# 004 (in this example the ID#s are non unique but they are numeric as opposed to text; the leading zeros are added by the cell's custom format).

A different variant of this which I also require would, instead of going to the cell which contains the lookup value, filter the Table to show only those records with ID# 004.  I realize a user can simply click the Filter arrow under column heading ID#, deselect All and select the desired filter value but this is tedious when many users have to scroll through lists which are hundreds of numbers long, dozens of times a day.  Ideally we would have a 'Show' button adjacent to the cell in which the user types in the lookup value and a 'ShowAll' button below that which clears all filter criteria.

Thanks for your help.
Avatar of Steve
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FarWest

assuming that you have a cell for filter in the sheet in $A$3
you can add this to sheet code that will filter if that value changed

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$A$3" Then

Dim ii As Integer
Dim ws As Worksheet
Set ws = ActiveSheet
For ii = 5 To ws.UsedRange.Rows.Count
If InStr(CStr(ws.Cells(ii, 1)), Target.Value) > 0 Then
ws.Rows(ii).EntireRow.Hidden = False
ws.Rows(ii).EntireRow.Hidden = True
End If
End If
End Sub

Open in new window

Avatar of qeng


Sorry for the delay; just able to get back to this now.

Steve:  thanks, your solution does work and has the benefit of leaving the original (full table) intact, so I don't have to filter and unfilter.  The part I'm not clear on though is:  am I right in assuming that for this to work, the validation list in the rows added above the main table needs to be built with the ID#s.  For example, if I added a row to your example, with ID#010, the 'filter' validation list in the rows above doesn't autoupdate.  My problem here is that my lists are hundreds of items long and are dynamic (the id #s change, some get added, some get deleted)
Avatar of qeng



I wasn't able to get the code you provided to run (I was careful to check to make sure I was putting a value in cell $A$3 to trigger "Worksheet_Change(ByVal Target As Range)".  Putting a value in that cell, changing it, hitting enter, or double-clicking wouldn't trigger the macro.  I tried making the macro public and to run it explicitly, by name, but the marco wouldn't show up.

I did develop some alternative code which does what I need though.  I'm able to filter the table to a value I type in, in a given lookup cell.  I'm also able to unfilter the table if i delete the value in the lookup cell.

What I need to do now is to open the hyperlinked filename in the second column of my Table (in my example above, assume that the Titles are hyperlinked) based on the value of 'lookup' in the first row.

So in my example, with a user typing "5" in the lookup cell, the macro would cause the table to autofilter to just the row with ID# 005 (that part I have working) and would open the hyperlinked filename "Title5a" from the adjacent cell to the right of ID#
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.