VBA宏报错1004：如何将Hyperlinks.Add的Anchor指定为偏移单元格？

错误根源 #

1. Anchor参数引用错误：companyOverview本身就是一个Range对象，无需再用.Range(companyOverview)包裹，这种冗余写法会让Excel无法识别有效的单元格引用，直接触发1004错误。
2. SubAddress字符串拼接混乱：原代码里的引号嵌套逻辑错误，导致工作表名称的引用格式不符合VBA要求。
3. 冗余的Find操作：其实不需要用Find查找刚插入的公司名称位置——插入时我们已经明确知道单元格位置（companies(lPosition + 1)），直接基于这个单元格偏移到C列即可，既高效又避免查找失败的风险。

修正后的完整代码 #

Sub Button1_Click()
    Application.ScreenUpdating = False
    Dim companyList As Worksheet
    Dim companyName As Range
    Dim companies As Range
    Dim lPosition As Long
    Dim insertedCompanyCell As Range
    Dim hyperlinkCell As Range
    Dim newSheetName As String
    
    ' 直接指定目标工作表，避免依赖ActiveSheet导致的意外错误
    Set companyList = ThisWorkbook.Worksheets("Company List")
    ' 动态获取B列已用数据范围，避免引用整列浪费性能
    Set companies = companyList.Range("B5:B" & companyList.Cells(companyList.Rows.Count, "B").End(xlUp).Row)
    Set companyName = companyList.Range("C2")
    newSheetName = CStr(companyName.Value)
    
    If Not IsEmpty(newSheetName) Then
        ' 按字母顺序查找插入位置
        lPosition = Application.WorksheetFunction.Match(newSheetName, companies, 1)
        ' 插入新单元格并写入公司名称
        companies(lPosition + 1).Insert
        Set insertedCompanyCell = companies(lPosition + 1)
        insertedCompanyCell.Value = newSheetName
        
        ' --- 创建新公司工作表（补充模板复制逻辑）---
        ThisWorkbook.Worksheets("模板").Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
        ActiveSheet.Name = newSheetName
        
        ' 直接获取C列对应单元格作为超链接锚点
        Set hyperlinkCell = insertedCompanyCell.Offset(0, 1)
        
        ' 插入指向新工作表的超链接
        companyList.Hyperlinks.Add Anchor:=hyperlinkCell, _
                                   Address:="", _
                                   SubAddress:="'" & newSheetName & "'!B2", _
                                   TextToDisplay:="Overview"
        
        ' 清空输入框
        companyName.ClearContents
    Else
        MsgBox "请输入公司名称"
    End If
    
    Application.ScreenUpdating = True
End Sub

关键修正说明 #

• 简化锚点引用：直接将hyperlinkCell（已偏移到C列的Range对象）传入Anchor参数，无需额外包装。
• 优化位置获取逻辑：利用插入操作时的已知位置直接定位，删除冗余的Find步骤，避免因重名、查找范围问题导致的定位失败。
• 修正超链接地址格式：用单引号包裹工作表名称（兼容含空格/特殊字符的表名），正确拼接SubAddress参数格式。
• 增强代码稳定性：将ActiveSheet改为明确指定工作表，动态获取数据范围，避免因环境变化导致的错误。

内容的提问来源于stack exchange，提问作者Progolfer79