VBA Loop to update a hyperlink for each cell in a column

Question

I have edited this question because I made a small mistake and decided not to dumb down the cell references I am using to simple ones.

I have chosen to update a hyperlink in a specific cell by running a macro.

Range("AB118").Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
Range("Z118") & Range("AD105")

So basically:

Column AB contains the final hyperlink Eg. Home!AB118

Column Z contains the name of the sheet Eg. Home!

Cell AD105 contains the cell which you will land Eg. AB118

I have written the macro to update the hyperlink one cell/row at a time.

 'Update "Sheet 1" Link
    Range("AB118").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    Range("Z118") & Range("AD105")

'Update "Sheet 2" Link
    Range("AB119").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    Range("Z119") & Range("AD105")

'Update "Sheet 3" Link
    Range("AB120").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    Range("Z120") & Range("AD105")

Is there a way to use "For Each" or similar, to shorten this macro which goes through each line combining column Z & cell AD105 into a hyperlink which appears in Column AB.

Hopefully this will make sense, I'm quite experienced with excel and VBA, but new to the forum.

The table of this information starts at row 118 and ends at row 171, So I am not sure if I need to tell it to start on a specific row and end on a specific row.

Answer 1

You can use the following Sub to cycle through Column "B" and "C" until one of them is empty. This will populate Column "A" with the values from "B" and "C" until there are no more contiguous values left.

Private Sub NewLink()
    Dim i As Integer
    i = 1
    Do Until IsEmpty(Range("B" & i).Value) Or IsEmpty(Range("C" & i).Value)
        Range("A" & i).Select
        ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
        Range("B" & i) & Range("C" & i)
        i = i + 1
    Loop
End Sub

Just use the following to call this function wherever you need it/want it to run.

Call NewLink

Following Dude_Scott's recommendation for .Select, use the following.

Private Sub NewLink()
    Dim i As Integer
    i = 1
    Do Until IsEmpty(Range("B" & i).Value) Or IsEmpty(Range("C" & i).Value)
        ActiveSheet.Hyperlinks.Add Anchor:=Range("A" & i), Addreess:="", SubAddress:= _
        Range("B" & i) & Range("C" & i)
        i = i + 1
    Loop
End Sub

EDIT: To assist readers in selecting a proper solution for their needs.

The pros to the 'Do While' loop are that you can add additional, consecutive rows without having to update your macro/sub.

The cons are that columns "B" and "C" are essentially reserved for creating links in column "A".

Answer 2

You could have an error in your code to perform the hyperlink if this isn’t addressed in the cell value. You need to make sure it the reference is setup like a sheet range reference.

SubAddress:= Range("Z118") & Range("AD105")

Should be

SubAddress:= "'" & Range("Z118") &"'!" & Range("AD105")

But since this is handled in you cell values, you have a couple options from here. But here is a For loop like you asked for (see TinMan464 for a While loop):

Sub foo() 
Dim i as integer 

  For i = 118 to 200 ‘change the last number to what you need 
     Range("AB"&i).Select
     ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= "'" & Range("Z"&i) &"'!" &  Range("AD105")
  Next i

End sub

But I would avoid using select

Sub foo()
Dim i as integer

  For i = 1 to 3 ‘change the last number to what you need 
      ActiveSheet.Hyperlinks.Add Anchor:= Range("AB"&i), Address:="", SubAddress:= "'" & Range("Z"&i) &"'!" &  Range("AD105")
  Next i

End sub

Answer 3

We already have an accepted answer using a standard For loop, but here is a version using For Each, With, and Offset for reference:

Dim WorkingCell As Range

For Each WorkingCell In ActiveSheet.Range("AB118:AB171").Cells
    With WorkingCell
        .Hyperlinks.Add Anchor:=.Cells(1,1), Address:="", _
            SubAddress:=.Offset(0,-2).Value & ActiveSheet.Range("AD105").Value
    End With
Next WorkingCell