I am trying to create hyperlinks based on the cells content in a user selected range of cells. I have gotten this far however when it runs it cycles through loop but does not create any hyperlinks.
我正在尝试根据用户选择的单元格范围中的单元格内容创建超链接。我已经做了这么多,但是当它循环循环,但没有创建任何超链接。
Sub AcctHyperlink()
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", "Select Range", WorkRng.Address, Type:=8)
For i = WorkRng.Rows.Count To 1 Step -1
If WorkRng.Cells(i, 1).Value <> "" Then
WorkRng.Cells(i, 1).Hyperlink.Add Anchor:=WorkRng.Cells(i, 1), _
Adress:="https://example.com/" & WorkRng.Cells(i, 1).Value & "/search", _
TextToDisplay:=WorkRng.Cells(i, 1).Value
End If
Next
End Sub
2 个解决方案
#1
1
Edited Nothing more than two typos and missing CStr()
calls! Hyperlink
should be Hyperlinks
, and Adress
should be Address
. The code you have compiles fine because Range.Item
returns a Variant
, not a Range
, so Excel can't flag such errors at compile time. The following works on my Excel 2013 installation:
只编辑了两个拼写错误和丢失的CStr()调用!超链接应该是超链接,地址应该是地址。您所编译的代码很好,因为范围。项返回一个变量,而不是范围,因此Excel不能在编译时标记此类错误。我的Excel 2013安装工作如下:
Option Explicit '<--- always use this for more robust code
Sub AcctHyperlink()
Dim WorkRng As Range
'On Error Resume Next '<--- Omit for error checking
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", "Select Range", WorkRng.Address, Type:=8)
Dim i as Long '<--- Need this because of Option Explicit
Dim addr as String '<--- ditto
For i = WorkRng.Rows.Count To 1 Step -1
If WorkRng.Cells(i, 1).Value <> "" Then
addr = "https://insight.metavante.org/opstopb1/OpstopServlet/Search?activityID=ViewProfileLnNote&activityType=note&activityTrgtID=undefined&activityAction=search&profileView=&accountNumber=" & CStr(WorkRng.Cells(i, 1).Value) & "&accountType=&subAccountNumber=&prcsGrpID=136&RelatedFIs=136&searchBy=account"
' Note: need CStr()
' V--- "Hyperlinks"
WorkRng.Cells(i, 1).Hyperlinks.Add Anchor:=WorkRng.Cells(i, 1), _
Address:=addr, _
TextToDisplay:=CStr(WorkRng.Cells(i, 1).Value)
'^--- "Address" two lines up
' ^^^^---- Need CStr()
End If
Next
End Sub
#2
0
you must be change :
你必须改变:
Adress
by Address
and Hyperlink
by Hyperlinks
地址连接和超链接连接
#1
1
Edited Nothing more than two typos and missing CStr()
calls! Hyperlink
should be Hyperlinks
, and Adress
should be Address
. The code you have compiles fine because Range.Item
returns a Variant
, not a Range
, so Excel can't flag such errors at compile time. The following works on my Excel 2013 installation:
只编辑了两个拼写错误和丢失的CStr()调用!超链接应该是超链接,地址应该是地址。您所编译的代码很好,因为范围。项返回一个变量,而不是范围,因此Excel不能在编译时标记此类错误。我的Excel 2013安装工作如下:
Option Explicit '<--- always use this for more robust code
Sub AcctHyperlink()
Dim WorkRng As Range
'On Error Resume Next '<--- Omit for error checking
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", "Select Range", WorkRng.Address, Type:=8)
Dim i as Long '<--- Need this because of Option Explicit
Dim addr as String '<--- ditto
For i = WorkRng.Rows.Count To 1 Step -1
If WorkRng.Cells(i, 1).Value <> "" Then
addr = "https://insight.metavante.org/opstopb1/OpstopServlet/Search?activityID=ViewProfileLnNote&activityType=note&activityTrgtID=undefined&activityAction=search&profileView=&accountNumber=" & CStr(WorkRng.Cells(i, 1).Value) & "&accountType=&subAccountNumber=&prcsGrpID=136&RelatedFIs=136&searchBy=account"
' Note: need CStr()
' V--- "Hyperlinks"
WorkRng.Cells(i, 1).Hyperlinks.Add Anchor:=WorkRng.Cells(i, 1), _
Address:=addr, _
TextToDisplay:=CStr(WorkRng.Cells(i, 1).Value)
'^--- "Address" two lines up
' ^^^^---- Need CStr()
End If
Next
End Sub
#2
0
you must be change :
你必须改变:
Adress
by Address
and Hyperlink
by Hyperlinks
地址连接和超链接连接