Hyperlink With Special Characters

[juan4412]

I am trying to create hyperlinks that link to worksheets that have names in column B, but add the hyperlink to column O. This is my syntax, but it produces an error of
Invalid Procedure Call Or Argument on the ActiveSheet line.

[vba]
Function CreateHyperlink()
Dim lr As Long, i As Long, ws As Worksheet


Set ws = ActiveSheet
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
If ws.Cells(i, 2).Value Like "*,*" Or ws.Cells(i, 2).Value Like "*'*" Then
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:="'" & ws.Cells(i, 2).Value & "'!A1", TextToDisplay:=ws.Cells(i, 15)
Else
ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value & "!A1", TextToDisplay:=ws.Cells(i, 15)
End If
Next i
ws.Activate
End Function
[/vba]

[offthelip]

You need to modify your code to something like this:
Note I have changed this to a subroutine, a function can only write into a single cell.

[VBA]Sub CreateHyperlink()
Dim lr As Long, i As Long, ws As Worksheet
Dim addr As String
Dim text2d As String


Set ws = ActiveSheet
With ws
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
If ws.Cells(i, 2).Value Like "*,*" Or ws.Cells(i, 2).Value Like "*'*" Then
' ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:="'" & ws.Cells(i, 2).Value & "'!A1", TextToDisplay:=ws.Cells(i, 15)
addr = "'" & ws.Cells(i, 2).Value & "'!A1"
text2d = Cells(i, 2)
.Hyperlinks.Add Anchor:=.Range(Cells(i, 15), Cells(i, 15)), _
Address:="", _
SubAddress:=addr, _
TextToDisplay:=text2d




Else
' ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value & "!A1", TextToDisplay:=ws.Cells(i, 15)
' modify as above
End If
Next i
End With
ws.Activate
End Sub


[/VBA]