Trying to add hyperlinks between main sheet and sheet in iteration, Run time error 5

[jpk89]

Invalid Procedure Call or Argument


I'm not sure why I'm getting this error I copied the hyperlinks.add syntax from a previous macro I had written that works fine... Any help would be appreciated


Sub MCM_CV_RESET()



Dim W As Worksheet
Dim M As Worksheet

Dim Cel As Range

Dim RefPtW As Range
Dim RefPtM As Range

Dim R1 As Range
Dim R2 As Range

Dim CVal_M1 As Range
Dim CVal_M2 As Range
Dim CVal_W1 As Range
Dim CVal_W2 As Range


Dim StoreVal As Variant


Set M = ThisWorkbook.Worksheets("Main")

For Each W In ThisWorkbook.Worksheets
If W.Index > 4 Then

Set RefPtW = W.UsedRange.Cells.Find(what:="MCM Chosen Value")
Set RefPtM = M.UsedRange.Cells.Find(what:=W.Cells(1, 1).Text)

If Not RefPtW Is Nothing And Not RefPtM Is Nothing Then

Set R1 = W.Range("B4:D9")
Set R2 = W.Range("A4:A9")

Set CVal_M1 = RefPtM.Offset(0, 2)
Set CVal_M2 = RefPtM.Offset(0, 3)

Set CVal_W1 = RefPtW.Offset(0, 1)
Set CVal_W2 = RefPtW.Offset(0, 5)

StoreVal = CVal_W2.Value

For Each Cel In R1
If IsEmpty(Cel) = False Then
Cel = Cel.Offset(0, 4)
Cel.Offset(0, 4).ClearContents
End If
Next Cel

For Each Cel In R2
If IsEmpty(Cel) = False Then
Cel.Offset(0, 5).Value = "N/A"
Cel.Offset(0, 6).Value = "No"
Cel.Offset(0, 7).Value = "No"
End If
Next Cel


CVal_W1.Value2 = StoreVal

CVal_W2.Formula = "=IF(COUNTIF($H$5:$H$9,""yes"")=1,(IF($F$9=""N/A"", 0,$F$9))* ($H$9=""yes"")+(IF($F$8=""N/A"", 0,$F$8))*($H$8=""yes"")+(IF($F$7=""N/A"", 0,$F$7))*($H$7=""yes"")+(IF($F$6=""N/A"", 0,$F$6))*($H$6=""yes"")+(IF($F$5=""N/A"", 0,$F$5))*($H$5=""yes"")+(IF($F$4=""N/A"", 0,$F$4))*($H$4=""yes""),""Uses Alternative Value"")"

CVal_M2.Formula = "=" & W.Name & "!" & CVal_W2.Address

W.Hyperlinks.Add CVal_W1, "", CVal_M1.Address(External:=True), TextToDisplay:=CVal_W1.Value2
W.Hyperlinks.Add CVal_W2, "", CVal_M2.Address(External:=True), TextToDisplay:=CVal_W2.Value2

'****** ERROR appears on the line below,

M.Hyperlinks.Add CVal_M1, "", CVal_W1.Address(External:=True), TextToDisplay:=CVal_M1.Value2
M.Hyperlinks.Add CVal_M2, "", CVal_W1.Address(External:=True), TextToDisplay:=CVal_M2.Value2


End If

End If


Next W

End Sub

 

[AFPathfinder]

I haven't dealt with hyperlinks much in VBA, but if I had to guess, when looking at Hyperlinks.Add in the object library, it looks like your second argument "Address As String" shouldn't be "". Here's what it says in the library: Function Add(Anchor As Object, Address As String, [SubAddress], [ScreenTip], [TextToDisplay])

 

[jpk89]

My assumption was that using "" would set it to the same address as the parent object but I had no clue if that was accurate or not... I had used "" in other bits of code that ran just fine so I know that at the very least it can sometimes be used. I ended up going with a work around that left the old hyperlinks in place but I'd still like to know what I was doing wrong.