excel - 当我使用相对范围时，VBA 会自动将我的范围转换为绝对值

Question

我有一张表格，我在公式中使用了几个命名范围。这些命名范围中有相对引用。每年我都必须复制表格，删除旧名称并分配新名称。我试图让整个工作簿以编程方式完成所有这些以节省时间。

我试图避免使用偏移函数，我在网上找到的任何东西都只告诉我如何用函数转换它，但对于公式(不是我需要的范围)。下面的代码采用我的范围并使它们都是绝对的.我如何让他们保持相对关系？我已经尝试过 R1C1，我认为它只对公式有效。我用作代码开始的站点是:https://powerspreadsheets.com/vba-create-named-range/

Option Explicit

Sub createNamedRange()  'https://powerspreadsheets.com/vba-create-named-range/
Dim myWorksheet As Worksheet
Dim i As Byte
Dim myNamedRange As Range
Dim myRangeName As String

Set myWorksheet = ThisWorkbook.Worksheets("Sheet1")     'identify worksheet containing cell range, and cell range itself (below)...

For i = 1 To 12         'i represents each month. we'll set range dependent upon month

Select Case i

Case Is = 1
    Set myNamedRange = myWorksheet.Range("B8")

Case Is = 2
    Set myNamedRange = myWorksheet.Range("B14,B8")

Case Is = 3
    Set myNamedRange = myWorksheet.Range("B20,B14,B8")

Case Is = 4
    Set myNamedRange = myWorksheet.Range("B30,B20,B14,B8")

Case Is = 5
    Set myNamedRange = myWorksheet.Range("B36,B30,B20,B14,B8")

Case Is = 6
    Set myNamedRange = myWorksheet.Range("B42,B36,B30,B20,B14,B8")

Case Is = 7
    Set myNamedRange = myWorksheet.Range("B52,B42,B36,B30,B20,B14,B8")

Case Is = 8
    Set myNamedRange = myWorksheet.Range("B58,B52,B42,B36,B30,B20,B14,B8")

Case Is = 9
    Set myNamedRange = myWorksheet.Range("B64,B58,B52,B42,B36,B30,B20,B14,B8")

Case Is = 10
    Set myNamedRange = myWorksheet.Range("B74,B64,B58,B52,B42,B36,B30,B20,B14,B8")

Case Is = 11
    Set myNamedRange = myWorksheet.Range("B80,B74,B64,B58,B52,B42,B36,B30,B20,B14,B8")

Case Is = 12
    Set myNamedRange = myWorksheet.Range("B86,B80,B74,B64,B58,B52,B42,B36,B30,B20,B14,B8")
End Select

myRangeName = MonthName(i) & "Denominator2019"      'specify defined name
ThisWorkbook.Names.Add Name:=myRangeName, RefersTo:=myNamedRange    'create named range with workbook scope. Defined name and cell range are as specified

Next i      'run back through loop and complete other months

End Sub

Answer 1

在添加相对命名范围的同时录制宏我得到这个:

ActiveWorkbook.Names.Add Name:="test4", _
                         RefersToR1C1:= "=Sheet1!R[1]C,Sheet1!R[1]C[2],Sheet1!R[1]C[4]"

创建名称时，我选择了 B10，并为范围地址添加了“B11，D11，F11”

也许你可以稍微解释一下你想如何使用你的相对范围？

编辑:像这样的东西 -

Sub createNamedRanges()  'https://powerspreadsheets.com/vba-create-named-range/
    Dim myWorksheet As Worksheet, i As Long
    Dim myNamedRange As Range, sRng As String, arr
    Dim myRangeName As String, sep As String

    Set myWorksheet = ThisWorkbook.Worksheets("Sheet1")
    arr = Split("B86,B80,B74,B64,B58,B52,B42,B36,B30,B20,B14,B8", ",") 'array(0 to 11)

    sRng = ""
    sep = ""
    For i = 1 To 12

        sRng = arr(11 - (i - 1)) & sep & sRng
        sep = "," 'add separator after first loop pass
        Set myNamedRange = myWorksheet.Range(sRng)
        myRangeName = MonthName(i) & "Denominator2019"

        ThisWorkbook.Names.Add Name:=myRangeName, _
                RefersToR1C1:=myNamedRange.Address(False, False, xlR1C1, True)
    Next i

End Sub