VBA to reference a range of cells (instead of one)

skpper

New Member
Joined
Dec 7, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
Hi,

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

VBA Code:
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. Would appreciate if anyone can jump in to help me with the syntax:

VBA Code:
Sub GoToAnotherCellInAnotherSheetInTheSameWorkbook()


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
 
Last edited by a moderator:

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

skpper

New Member
Joined
Dec 7, 2020
Messages
10
Office Version
  1. 2010
Platform
  1. Windows
Found a solution!

VBA Code:
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
 
Last edited by a moderator:
Solution
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,194
Messages
5,768,778
Members
425,494
Latest member
Ragamacam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top