Hyperlink Syntax Issue

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.

i'm sorry - neither IE or chrome will allow me to open the attachment window.

the pop-up is appearing but there is nothing within the window.

i can email the file?

any way you think you can try without having the file?

i'm starting to think this simply won't work in VB.

just as a reminder, it has to be in this format:

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

sorry for the delayed response - in some meetings.

i've went through it so, so slowly and continue to get an end of statement or application defind or object defined error. here is the entire code:

also, this DID work, meaning it pasted into excel, however, the hyperlink code itself does not function. it needs the #.

For Each NewCell3 In CCTOCList
        Range("I" & currentRow2) = "=hyperlink(" & Range("O" & currentRow2).Value & "!BK9,""" & Range("O" & currentRow2).Value & """)"
        currentRow2 = currentRow2 + 1
Next NewCell3

Sub RunHyperLinks()

Dim ws As Worksheet
Dim NewCell2 As Range
Dim arC
Dim Lastrow As Integer
Dim ARC2 As Range
Dim CurrentRow As Integer
Dim NewCell3 As Range
Dim CCTOCList As Range
Dim currentRow2 As Integer
Dim AddressR As Range


Set ws = Sheets("Key")
arC = ws.Range("i2").Value
Range(arC).Copy


Sheets("Control Page").Range("I6").PasteSpecial Paste:=xlPasteValues
Sheets("Control Page").Activate

Lastrow = Range("I65536").End(xlUp).Row
Set ARC2 = Range("J6:J" & Lastrow)
CurrentRow = 6

For Each NewCell2 In ARC2
    Range("J" & CurrentRow).Value = "=VLOOKUP(I" & CurrentRow & ",CCLIST,2,FALSE)"
    CurrentRow = CurrentRow + 1
Next NewCell2
        
Set CCTOCList = Range("i6:i" & Lastrow)

currentRow2 = 6

Range("I6:I" & Lastrow).Copy
Range("o6").Select
Sheets("Control Page").Range("o6").PasteSpecial Paste:=xlPasteValues


For Each NewCell3 In CCTOCList
        Range("I" & currentRow2) = "=HYPERLINK(" & """#'""" & Range("O" & currentRow2).Value & "'!BK9,""" & Range("O" & currentRow2).Value & """)"
        currentRow2 = currentRow2 + 1
Next NewCell3


Range("O6:O" & Lastrow).Delete
Range("A1").Select


End Sub

still getting the application defined error...

maybe let's try to simplify it by taking the ranges out and just making the cells static. once the code will go into excel correctly then maybe move the dynamic ranges in?

i got it:

Range("I" & currentRow2) = "=HYPERLINK(""#" & Range("O" & currentRow2).Value & "!BK9"",""" & Range("O" & currentRow2).Value & """)"

thanks for the help!