All,

I've been trying for a very long time to get a dynamic hyperlinking formula in vba to work correctly. I was having issues with the simple =Hyperlink() function but found a solution to have the formula work correctly. i have some ranges i need to use within the formula but i can't get the syntax. i've been working on and off on this for 3 days with no success. can anyone take a look:

For Each NewCell3 In CCTOCList
        Range("I" & currentRow2) = "=HYPERLINK(" & """#'""" & Range("O" & currentRow2).Value & "'!BK9,""" & Range("O" & currentRow2).Value & """)"
        currentRow2 = currentRow2 + 1
Next NewCell3
what i would like to appear in excel:

=HYPERLINK("#'OPRATIONS1'!BK9","OPRATIONS1")

the range 06 would be "OPRATIONS1", the name of the tab.

Thank you

edit: i am getting an "Application-defined or object defined error" message.