将VBA公式转换为R1C1格式

Question

请帮助将以下公式转换为R1C1格式：

ActiveSheet.PageSetup.PrintArea = "$A$1:$L$49"

谢谢！

Answer 1

如果您可以访问行和列的编号，我建议您永远不要使用完整的R1C1表示法，如下所示：

Sub test()
ActiveSheet.PageSetup.PrintArea = rc2a1(1, 1, 49, 12)
End Sub

Function rc2a1(start_row As Long, start_col As Integer, Optional end_row As Long, Optional end_col As Integer)
Dim address As String
address = ThisWorkbook.Worksheets(1).Cells(start_row, start_col).address

If end_row > 0 And end_col > 0 Then
address = address & ":" & ThisWorkbook.Worksheets(1).Cells(end_row, end_col).address
End If

rc2a1 = address
End Function

如果你有绝对的R1C1表示法，那么这就是方法：

Sub test()
ActiveSheet.PageSetup.PrintArea = rc2a1("r1c1:r49c12")
End Sub

Function rc2a1(reference As String)
Dim x As Byte
Dim refs As Variant
Dim parts As Variant
refs = Split(reference, ":")

For x = 0 To UBound(refs)
parts = Split(UCase(refs(x)), "C")
parts(0) = Mid(parts(0), 2)
refs(x) = ThisWorkbook.Worksheets(1).Cells(CLng(parts(0)), CLng(parts(1))).address
Next

rc2a1 = Join(refs, ":")
End Function