Excel宏VBA根据活动单元格内容创建到另一个表的超链接

I am trying create a hyperlink that links to a cell in another sheet in the same workbook which bases on the text of the cell. I've tried to do it manually and here is the result :

我正在尝试创建一个超链接，链接到另一个工作簿中的单元格，该工作簿基于单元格的文本。我试过手动操作，结果如下:

    =HYPERLINK("[Five Ecom - Floor Plan.xlsx]Inventory!" & 
     ADDRESS(MATCH('8th'!F9,Inventory!$A$1:$A$2000,0),1),'8th'!F9)

'8th'!F9 - is the current cell
“8”!F9 -是当前单元格
Inventory!$A$1:$A$2000 - range of the cell destinations
库存!$1:$ $ $2000 -范围内的细胞目的地。

The friendly text would be the content of the same current cell.

友好的文本将是相同当前单元格的内容。

I'm particularly having difficulties on string manipulation here so I decided to separate the code into several variables, to which up to now I am unable to produce the desired output and results into Run-time error '1004'.

我在这里的字符串操作上特别困难，所以我决定将代码分解为几个变量，直到现在我无法生成所需的输出，并导致运行时错误“1004”。

Public Sub Convert_To_Hyperlinks()
    Dim cell As Range
    Dim x As String
    Dim y As String
    Dim z As String
    Dim c As String

    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
        If cell <> "" Then
            c = ActiveCell.Value
            x = "=HYPERLINK(""[Five Ecom - Floor Plan.xlsm]Inventory!"" & ADDRESS(MATCH("""
            y = """, Inventory!$A$1:$A$2000,0),1),"""
            z = """)"
            ActiveCell.FormulaR1C1 = x & c & y & c & z
        End If
    Next
End Sub

1 个解决方案

#1

Your macro will work if you replace:

如果您替换:

ActiveCell.FormulaR1C1 = x & c & y & c & z

with:

ActiveCell.Formula = x & c & y & c & z

This assumes that the Match() finds what it is looking for.

For all the cells in Selection use:

这假定Match()找到它要查找的内容。对于所有选择使用的单元格:

Public Sub Convert_To_Hyperlinks()
    Dim cell As Range
    Dim x As String
    Dim y As String
    Dim z As String
    Dim c As String

    For Each cell In Intersect(Selection, ActiveSheet.UsedRange)
        If cell <> "" Then
            c = cell.Value
            x = "=HYPERLINK(""[Five Ecom - Floor Plan.xlsm]Inventory!"" & ADDRESS(MATCH("""
            y = """, Inventory!$A$1:$A$2000,0),1),"""
            z = """)"
            cell.Formula = x & c & y & c & z
        End If
    Next
End Sub

(Assuming you need the content of each cell to be the "friendly name")

(假设您需要每个单元格的内容为“友好名称”)

#1