我有一个工作表,我在公式中使用了几个命名范围。这些命名范围在其中具有相对引用。每年我都必须复制工作表,删除旧名称并分配新名称。我正在尝试让整个工作簿以编程方式完成所有这些操作以节省时间。
我试图避免使用偏移函数,我在网上找到的任何内容都只告诉我如何使用函数转换它,但对于公式(不是我需要的范围)。下面的代码采用了我的范围,并使它们都是绝对的。我如何让他们保持相对?我尝试过 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
在添加相对命名范围的同时录制宏 我得到这个:
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
这正是我想要做的:
Sub createNamedRange()
Dim myWorksheet As Worksheet
Dim i As Byte
Dim myRangeName As String
Set myWorksheet = ThisWorkbook.Worksheets("Sheet1") 'identify worksheet containing cell range, and cell range itself (below)...
myWorksheet.Range("B1").Select
For i = 1 To 12 'i represents each month. we'll set range dependent upon month
myRangeName = MonthName(i) & "Denominator2019" 'specify defined name
Select Case i
Case Is = 1
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[7]C" 'create named range with workbook scope. Must use r1c1 for relative references.
Case Is = 2
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 3
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 4
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 5
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 6
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 7
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 8
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[57]C,Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 9
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[63]C,Sheet1!R[57]C,Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 10
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[73]C,Sheet1!R[63]C,Sheet1!R[57]C,Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 11
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[79]C,Sheet1!R[73]C,Sheet1!R[63]C,Sheet1!R[57]C,Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
Case Is = 12
myWorksheet.Names.Add Name:=myRangeName, RefersToR1C1:="=Sheet1!R[85]C,Sheet1!R[79]C,Sheet1!R[73]C,Sheet1!R[63]C,Sheet1!R[57]C,Sheet1!R[51]C,Sheet1!R[41]C,Sheet1!R[35]C,Sheet1!R[29]C,Sheet1!R[19]C,Sheet1!R[13]C,Sheet1!R[7]C"
End Select
Next i 'run back through loop and complete other months
End Sub