excel indexing using vba

Posted on 2012-09-11
Medium Priority
Last Modified: 2012-09-12
Hi All,
I need to select a cell (or row) in second sheet based on a user selection in the first sheet. I would probably use the selection-changed event to triger the function. i.e. first sheet contains a list of section-headers that apply to sections in the second sheet.
Using hyperlinks and anchors isn't suitable because the user will be adding sections (and associated titles) frequently. Ideally I need a function that returns the row of the first occurence of the search string in the second sheet,  so I can select that row in the second sheet.
Question by:COACHMAN99
LVL 18

Accepted Solution

krishnakrkc earned 2000 total points
ID: 38389556

Try something like this. This goes in the source worksheet module.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    If Target.Column <> 1 Then Exit Sub 'search string is in Col A
    Dim rngFound    As Range
    If Not Intersect(Me.UsedRange, Target) Is Nothing Then
        'adjust the destination worksheet name and range
        Set rngFound = Worksheets("Sheet2").Range("a:a").Find(what:=Target.Value, lookat:=xlWhole)
        If Not rngFound Is Nothing Then
            Application.Goto rngFound
        End If
    End If
    Cancel = True
End Sub

Open in new window

To work this code, double click the search string cell.


Author Closing Comment

ID: 38391468
Excellent Kris, thanks very much!

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question