excel vba: insert formula with dynamic cell reference using vba?

Question

I am inserting the following hyperlink as a formula using vba:

ActiveSheet.Range("AD" & ActiveCell.Row).Formula = "=HYPERLINK(""S:\Tasks\" & Range("C" & ActiveCell.Row).Value & "\log.txt"",""View Log"")"

This works fine, however if my value in cell C was to change then my hyperlink becomes invalid and won't work because it has retained the value of the cell at the time in which the formula was entered.

I need a way of making a dynamic reference to my cell C in the event that the value should change then my link will also change.

Can someone please show me how to do this? Thanks in advance

Answer 1

Your code is taking the value from column C and building a string using that value that looks like this:

"S:\Tasks\FolderName\log.txt"

Instead, what you want to do is build the following string:

"S:\Tasks\" & C2 & "\log.txt"

To do that, use this VBA code:

ActiveSheet.Range("AD" & ActiveCell.Row).Formula = "=HYPERLINK(""S:\Tasks\"" & C" & ActiveCell.Row() & " & ""\log.txt"",""View Log"")"