+ Reply to Thread
Results 1 to 4 of 4

Link to row number from search page

Hybrid View

  1. #1
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Link to row number from search page

    The below code performs a search in the sheets of a workbook and then lists the search results on the search page that includes the sheet name and row number where the data is located. I would like to be able to include a link to each of the search results to make it easier to go to the data if a change needed to be made. Can anyone help me with this?





    Sub SearchSheets()
     Const shSearch As String = "Search" '<== Your example has a space at the end of sheet name Search
     Dim ws As Worksheet
     Dim FoundCell As Range
     Dim LastCell As Range
     Dim FirstAddr As String
     Dim SearchTerm As String
     
        With Worksheets(shSearch)
            .Range("f2", .Cells(Rows.Count, "h").End(xlUp).Offset(1)).ClearContents
            SearchTerm = .Range("b3")
        End With
        For Each ws In Worksheets
            With ws
                If .Name <> shSearch Then
                    With .Range("B:B")
                        Set LastCell = .Cells(.Cells.Count)
                    End With
                    Set FoundCell = .Range("B:B").Find(What:=SearchTerm, After:=LastCell, LookAt:=xlPart)
                    
                    If Not FoundCell Is Nothing Then
                        FirstAddr = FoundCell.Address
                    End If
                    Do Until FoundCell Is Nothing
                        With Worksheets(shSearch).Cells(Rows.Count, "f").End(xlUp)
                            .Offset(1) = ws.Name
                            .Offset(1, 1) = FoundCell.Offset(0, 0)
                            .Offset(1, 2) = FoundCell.Offset(0, 1)
                            .Offset(1, 3) = FoundCell.Offset(0, 2)
                            .Offset(1, 4) = FoundCell.Offset(0, 3)
                            .Offset(1, 5) = FoundCell.Offset(0, 4)
                            .Offset(1, 6) = FoundCell.Offset(0, 5)
                            .Offset(1, 7) = FoundCell.Offset(0, 6)
                            .Offset(1, 8) = FoundCell.Offset(0, 7)
                            .Offset(1, 9) = FoundCell.Offset(0, 8)
                            
                            
                            
                            
                            .Offset(1, 10) = FoundCell.Row
                        End With
                                        
                        Set FoundCell = .Range("B:B").FindNext(After:=FoundCell)
                        If FoundCell.Address = FirstAddr Then
                            Exit Do
                        End If
                    Loop
                End If
            End With
        Next
    End Sub

  2. #2
    Forum Guru
    Join Date
    03-02-2006
    Location
    Los Angeles, Ca
    MS-Off Ver
    WinXP/MSO2007;Win10/MSO2016
    Posts
    12,943

    Re: Link to row number from search page

    Try:
    With Worksheets(shSearch).Cells(Rows.Count, "f").End(xlUp)
        .Offset(1) = ws.Name
        ActiveSheet.Hyperlinks.Add _
            Anchor:=.Offset(1), _
            Address:="", _
            SubAddress:="'" & ws.Name & "'!H2", _
            TextToDisplay:=ws.Name
            .Offset(1, 1) = FoundCell.Offset(0, 0), etc.
    Ben Van Johnson

  3. #3
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Link to row number from search page

    That is exactly what I needed. Thank you so very much.
    The one thing I noticed however is that when you click on the link, it takes you to Cell H2 on the sheet. I'd like the hyperlink to take you to the row number when selected.
    Last edited by guitarsweety; 11-30-2023 at 09:54 AM.

  4. #4
    Forum Contributor guitarsweety's Avatar
    Join Date
    10-30-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2010
    Posts
    188

    Re: Link to row number from search page

    I added the below line to pronto Leah's code to get the desired. results. Thank you again for your help.

    SubAddress:="'" & ws.Name & "'!" & FoundCell.Address, _

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 06-24-2023, 02:11 AM
  2. Replies: 0
    Last Post: 01-20-2017, 01:53 PM
  3. Replies: 6
    Last Post: 11-17-2013, 01:40 PM
  4. Need to copy the link of a page directly into excel for a database of search keywords.
    By magicalgeniusak in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-03-2013, 02:36 AM
  5. link text to number on other page
    By vckim in forum Excel General
    Replies: 5
    Last Post: 06-01-2010, 05:10 PM
  6. [SOLVED] if i sort cell that has link to another page how to keep link
    By steve Bahrain in forum Excel General
    Replies: 1
    Last Post: 08-16-2006, 08:25 AM
  7. [SOLVED] set up a link that updates page one from all other page entries?
    By brensand in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2006, 03:19 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1