Search code examples
vbaexcelexcel-2013

Use Cell Value (Contains periods & Apastrophe) In Hyperlink Address

I want to take the value from cell A2 and use it as the hyperlink address with my VBA. For example, let's say that cell A2 has a value of Pink, I want to link to worksheet Pink!A1. Issue I have is that I get an error of

Syntax error

How should this be altered in order to be valid syntax?

Dim lr As Long, i As Long
lr = Cells(Rows.Count, 2).End(xlUp).Row
For i = 2 To lr
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Cells(i, 2).Value"!A1"
Next i

EDIT
For clarity let me add that I want the link to be placed in Column O, with the current cell value of Column O, but the worksheet to link to should be the value of column B.

EDIT 2
Using thte syntax suggested by @Jeremy has gotten me closer and I am at this point, where I found the issue was any special symbol (i.e. a comma, period, hyphen etc) must be enclosed with a single quote. Which leaves my syntax at the below, but get an error

Invalid Procedure call or argument

I think I am close to having this perfect but not quite there:

Function TakeTwo()
Dim lr As Long, i As Long
Dim sSheet As String
Dim 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 "*'*" Or ws.Cells(i, 2).Value Like "*&*" Or 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
Solution

  • Shooting without trying it: Change it to this:

      ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=Cells(i, 2).Value & "!A1"