Address:= .hyperlinks(1).Address subscript out of range

I'm getting a subscript out of range for the Address:= portion of the code below. Since I'm new to VBA and somebody else wote the original code (and have disappeared). I need to figure out what is going on.

While I really have no idea how the initial rw variable assignment works (seems messy, but someone else wrote it), the variable rw has the correct value at the point the error is thrown. I have also used the Range("BX" & rw).value object successfully in assign statements both above and below the error. So I know that's right. From what I've read ".Hyperlinks(1).Address" also seems to be the correct way to refer to the first hyperlink in a specific cell. So I'm stumped.

Can anyone tell me why the subscript is out of range?

Dim ad
Dim rw As Long
ad = ActiveCell.Address
rw = Mid(ad, InStr(2, ad, "$") + 1, 999)
Range("TE_Image").Hyperlinks.Add Anchor:=Range("TE_Image"), Address:=Range("BX" & rw).Hyperlinks(1).Address, screenTip:=screenTip, textToDisplay:=textToDisplay

That eliminated the error, but did not copy the hyperlink (only the cell value). I would think that ".Hyperlinks(1)." has to be part of the Address:= or SubAddress:= statement. Then again I'm a newb.

Since your variation gives no error, but not the result I'm looking for, I've tried to lay this out more completely below and shown my current version of the code (that throws subscript out of range errors).


Essentially I have 2 sheets in the same workbook. The first is laid out like a form, allowing the user to review data in a nice format. The second is like a database, where every row corresponds to a unique record. I need to write a "save" macro from the form-type sheet that can handle either of 2 circumstances.
1) the macro is saving a new record to a row of the target "database type" sheet
2) the macro could be saving a data updated record to a same row of the target sheet that it was retrieved from in the first place.

Ok, so I'm working on the part of the save macro that will copy a hyperlink (and preferably only the hyperlink, not colors/formats) and paste it into the database sheet.

I have tried what seems like dozens of variations all either ending in a subscript error (when I use the .hyperlinks(1).address method) or not generating an error, but also not copying the hyperlink (just the text value of the cell).

The hyperlink to be copied is in sheet TE_Form , in a named range "TE_Image" which is in cell C27

The destination will either be sheet LogDB cell BX6 (when this is a new record being added to LogDB) -or- sheet LogDB cell BX? (where the ? is the row number of an existing record in the DB_Log).

In the case when it is an existing record being copied to LogDB, I use the .Find method and a variable rw to identify which row within column BX that link should be pasted.


Here is what I've got now (boiled down from a long macro):

Sub link_copy_test()

    Dim RecID As Integer
    Dim screenTip As String
    Dim textToDisplay As String
    Dim FindRow As Variant
    Dim rw As Long

    screenTip = "Link to Chart Image"
    textToDisplay = "Chart Image"
    RecID = Worksheets("TE_Form").Range("A1").Value

    With Worksheets("LogDB")
        Set FindRow = Worksheets("LogDB").Range("C:C").Find(What:=RecID, LookIn:=xlValues)
        If Not FindRow Is Nothing Then
            rw = FindRow.Row
        End If
    End With

    If IsEmpty(Range("A1").Value) = True Then
        Range("TE_Image").Hyperlinks.Add Anchor:=Range("TE_Image"), Address:=Range("BX6").Hyperlinks(1).Address, screenTip:=screenTip, textToDisplay:=textToDisplay
    Else
        Range("TE_Image").Hyperlinks.Add Anchor:=Range("TE_Image"), Address:=Range("BX" & rw).Hyperlinks(1).Address, screenTip:=screenTip, textToDisplay:=textToDisplay
    End If

End Sub

And before I forget, I really appreciate the help.