Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Moving hyperlink addresses (Excel 2000 VBA)

    I have a list of hyperlinks, each in their own cell running down a column. The hyperlinks jump to another column to the right. For example the link in a2 jumps to and selects c1:c3; the link in a3 jumps to and selects c4:c6; etc.

    Problem: I need to add a column, which is going to make all the hyperlinks jump to the wrong cell. (In fact I've added the column and know that to be true). Now the hyperlink in a2 needs to jump to c2:c4; the hyperlink in a3 needs to jump to c5:c6; etc.

    Its a painstaking process to recreate all these links. It seems there ought to be a relatively painless macro I could run to ship to address (is that even the right term?) over one cell. I've done a good bit in Word VBA, but very little in Excel.

    Please help!!
    Troy

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving hyperlink addresses (Excel 2000 VBA)

    If you want to change the hyperlink target from C1:C3 to C2:C4, you seem to have inserted a row. To do this in code, use something like this:

    Dim rng As Range
    Dim hyp As Hyperlink
    Dim strAddress As String
    Set rng = Range("A1:A200")
    For Each hyp In rng.Hyperlinks
    ' Get address, shift down one row
    strAddress = Range(hyp.SubAddress).Offset(1, 0).Address
    hyp.SubAddress = strAddress
    hyp.TextToDisplay = strAddress
    Next hyp
    Set hyp = Nothing
    Set rng = Nothing

    If you actually want to shift the targets by a column instead of by a row, use Offset(0, 1) instead of Offset (1, 0).

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving hyperlink addresses (Excel 2000 VBA)

    Hans,
    Thanks for the speedy response!! That works great!!

    One question. Everything seems to work fine, but I noticed, that when I mouse over the hyperlink now, there are "$" between each row/column. Why is that?

    Thanks!!
    Troy

  4. #4
    4 Star Lounger
    Join Date
    Apr 2001
    Posts
    482
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Moving hyperlink addresses (Excel 2000 VBA)

    Hans,
    Thanks for both helping and teaching!!
    Troy <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Moving hyperlink addresses (Excel 2000 VBA)

    That's because Address by default returns absolute addresses.

    In Excel formulas, cell references can be absolute or relative. Absolute references are indicated by "$". For example: if you have the formula =SUM(A11) in cell E1, and you fill down this formula to the cells below it, you'll get =SUM(A22) in cell E2, etc., but if you had the formula =SUM($A$1:$D$1), it would be copied unchanged to the cells below. You can have mixed references: =A$1 means that the reference to the column (A) is relative and the reference to the row (1) is absolute).

    To get relative addresses, use Address(RowAbsolute:=False, ColumnAbsolute:=False) instead of just Address.

Posting Permissions

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