CODEWITHSUNDEEP
excelvba
skpper
skpper

Reputation: 13

How to reference a range of cells (dynamic referencing)?

I have the following code that creates hyperlinks to bring user to cells on another sheet. However, I would like to reference a range of cells instead of a single cell.

Sub GoToAnotherCellInAnotherSheetInTheSameWorkbook()
    
    Dim i_counter As Integer
    Dim i_output As Integer
    
    i_output = 14
    
    For i_counter = 16 To 20
        ActiveSheet.Hyperlinks.Add Range("F" + CStr(i_counter)), Address:="", SubAddress:="'" & Sheet7.Name & "'!D" & CStr(i_output), TextToDisplay:="Go"
        i_output = i_output + 132
    Next i_counter
    
End Sub

This is the solution that I have thought of. But the reference is not valid.

Sub GoToRangeOfCellsInAnotherSheetInTheSameWorkbook()
    
    Dim i_counter As Integer
    Dim i_output1 As Integer
    Dim i_output2 As Integer
    
    i_output1 = 14
    i_output2 = 42
    
    For i_counter = 16 To 20
        ActiveSheet.Hyperlinks.Add Range("F" + CStr(i_counter)), Address:="", SubAddress:="'" & Sheet7.Name & "'!A & CStr(i_output1): A & CStr (i_output2)", TextToDisplay:="Go"
        i_output1 = i_output1 + 132
        i_output2 = i_output2 + 132
    Next i_counter
    
End Sub

Upvotes: 0

Views: 81

Answers (1)

GWD
GWD

Reputation: 3998

I think the Stack Overflow syntax highlighting made it obvious where the problem is :) If only that existed in the VBA IDE...

Maybe it works if you replace this line:

ActiveSheet.Hyperlinks.Add Range("F" + CStr(i_counter)), Address:="", SubAddress:="'" & Sheet7.Name & "'!A & CStr(i_output1): A & CStr (i_output2)", TextToDisplay:="Go"

with this one...

ActiveSheet.Hyperlinks.Add Range("F" & CStr(i_counter)), Address:="", SubAddress:="'" & Sheet7.Name & "'!A" & CStr(i_output1) & ": A" & CStr(i_output2), TextToDisplay:="Go"

Upvotes: 1

Related Questions