Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post

    Bookmarks in excel (Excel xp)

    Is there a way to put a "bookmark" in excel? i need to search a 40,000 row excel file for the words "Total base". Everytime it finds one i want to move 3 rows down and copy that row to another worksheet and make it a hyperlink to that spot in the 40,000 row excel sheet. It will act as a quick index to the 40,000 row sheet. Is that possible in a macro? thanks

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Bookmarks in excel (Excel xp)

    You can use HYPERLINKS as "bookmarks"
    Does this do what you want? Change the sheet names and ranges as appropriate.

    <pre>Sub JHA900BookMark()
    Dim wks As Worksheet
    Dim wks2 As Worksheet
    Dim rng As Range
    Dim c As Range
    Dim FirstAddress As String
    Dim lRow As Long
    Set wks = Worksheets("Sheet1")
    Set wks2 = Worksheets("Sheet2")
    Set rng = Intersect(wks.UsedRange, wks.Range("A:A"))
    lRow = wks2.Range("a65536").End(xlUp).Offset(1, 0).Row
    With rng
    Set c = .Find("total base", _
    LookIn:=xlValues, _
    after:=.Cells(1))
    If Not c Is Nothing Then
    FirstAddress = c.Address
    Do
    wks2.Cells(lRow, 1).Value = c.Offset(3, 0).Value
    wks2.Hyperlinks.Add _
    Anchor:=wks2.Cells(lRow, 1), _
    Address:="", _
    SubAddress:="'" & wks.Name & "'!" & _
    c.Offset(3, 0).Address
    lRow = lRow + 1
    Set c = .FindNextŠ
    Loop While Not c Is Nothing And c.Address <> FirstAddress
    End If
    End With
    End Sub</pre>


    Steve

Posting Permissions

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