title: Use Cell Value (Contains periods & Apastrophe) In Hyperlink Address
body: <p>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 </p>
  
  <blockquote>
    <p>Syntax error</p>
  </blockquote>
  
  <p>How should this be altered in order to be valid syntax?</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>EDIT</strong><br>
  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.</p>
  
  <p><strong>EDIT 2</strong><br>
  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</p>
  
  <blockquote>
    <p>Invalid Procedure call or argument</p>
  </blockquote>
  
  <p>I think I am close to having this perfect but not quite there:</p>
  
  <pre><code>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 "*&amp;*" 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:="'" &amp; ws.Cells(i, 2).Value &amp; "'!A1", TextToDisplay:=ws.Cells(i, 15)
    Else
      ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value &amp; "!A1", TextToDisplay:=ws.Cells(i, 15)
    End If
  Next i
  ws.Activate
  End Function
  </code></pre>