在超链接地址中使用单元格值(包含句点和灾难)

Question

我想从单元格A2中获取值，并将其用作我的VBA的超链接地址。例如，假设单元格A2具有粉红色的值，我想链接到工作表粉红色！a1。我遇到的问题是我会遇到一个错误

语法错误

应该如何对其进行更改以成为有效语法？

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

编辑
为了清楚地说，让我补充说，我希望将链接放在o列中，并带有列的当前单元格值，但是要链接到的工作表应为列b。

的值。

编辑2
使用@Jeremy建议的Thte语法使我更加接近，此时我发现问题是任何特殊的符号（即逗号，期间，连字符等），必须带有单个报价。在下面留下我的语法，但会出现错误

无效的过程调用或参数

我认为我接近这个完美，但不完全存在：

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

Answer 1

射击而无需尝试：将其更改为：

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

Answer 2

您去这里：

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
    ActiveSheet.Hyperlinks.Add Anchor:=ws.Cells(i, 15), Address:="", SubAddress:=ws.Cells(i, 2).Value & "!A1", TextToDisplay:=ws.Cells(i, 15)
Next i
ws.Activate