如何在R1C1公式中使用变量？VBA语法问题求助

核心问题排查 #

• 变量名不一致：代码定义的是targetSheet，但公式里写的是tgtSheet，导致引用错误
• R1C1格式写错：
  • aCellr和aCellc是Range对象，得用.Row和.Column提取具体数值，不能直接把对象插入公式
  • 绝对引用的R1C1格式是R行号C列号，你写成了带方括号的相对引用格式，还多了空格（c [&aCellc&]里的空格要删掉）
• 代码结构缺失：最后一行End With没有对应的With开头，语法不完整

修正后的完整代码 #

Sub somesub()
    Dim strSearch As String
    Dim aCell As Range ' 无需分开定义行/列变量，一次查找即可获取行+列索引
    Dim targetSheet As Worksheet
    Dim sourceSheet As Worksheet
    Dim dataWorkbook As Workbook ' 注意：需确保此变量已提前定义并赋值

    Set sourceSheet = dataWorkbook.Worksheets("Glass Schedule")
    Set targetSheet = ThisWorkbook.Worksheets("QC")

    '---------------------------------------------------------------------------
    '查找"Opening #"所在单元格（同时获取行、列索引）
    '---------------------------------------------------------------------------
    With sourceSheet
        strSearch = "Opening #"
        Set aCell = .Range("A:M").Find(What:=strSearch, LookIn:=xlValues, _
        lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlDown, _
        MatchCase:=False, SearchFormat:=False)
    End With

    If Not aCell Is Nothing Then
        MsgBox (strSearch & " 找到啦，位置：行" & aCell.Row & "，列" & aCell.Column)
    Else
        MsgBox (strSearch & " 没找到")
        Exit Sub ' 未找到目标时直接退出，避免后续代码报错
    End If

    ' 修正后的动态公式
    With targetSheet ' 补充With语句，对应结尾的End With
        .Range("A7").Formula2R1C1 = _
        "=FILTER(FILTER(OFFSET('[Glass Schedule]Glass Schedule'!R" & aCell.Row & "C" & aCell.Column & ",0,0,3001,16),'[Glass Schedule]Glass Schedule'!R" & aCell.Row & "C4:R3008C4<>""""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})"
    End With
End Sub

关键修正点说明 #

1. 变量简化：将aCellr和aCellc合并为单个aCell，一次查找即可同时获取行号和列号，减少冗余代码
2. R1C1语法纠正：用aCell.Row和aCell.Column提取具体数字，公式中写成R" & 行号 & "C" & 列号 & "的格式，去掉错误的方括号和空格
3. 变量名统一：把公式中的tgtSheet改为与定义一致的名称，或直接写工作表名（跨工作簿引用时更稳妥）
4. 补全代码结构：给公式部分添加With targetSheet语句匹配结尾的End With，同时增加未找到目标时的退出逻辑

后续全动态化扩展 #

若要将OFFSET的宽高也改为动态变量，可先计算数据源的最后一行和列：

' 计算数据源的最后一行和最后一列
Dim lastRow As Long, lastCol As Long
With sourceSheet
    lastRow = .Cells(.Rows.Count, aCell.Column).End(xlUp).Row
    lastCol = .Cells(aCell.Row, .Columns.Count).End(xlToLeft).Column
End With

' 替换OFFSET的高度和宽度参数
With targetSheet
    .Range("A7").Formula2R1C1 = _
    "=FILTER(FILTER(OFFSET('[Glass Schedule]Glass Schedule'!R" & aCell.Row & "C" & aCell.Column & ",0,0," & lastRow - aCell.Row + 1 & "," & lastCol - aCell.Column + 1 & "),'[Glass Schedule]Glass Schedule'!R" & aCell.Row & "C4:R" & lastRow & "C4<>""""),{1,1,1,1,0,0,1,1,0,0,0,0,0,0,1,1})"
End With

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