excel indexing using vba

Posted on 2012-09-11
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


    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.

    LVL 7

    Author Closing Comment

    Excellent Kris, thanks very much!

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
    How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
    The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
    This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now