VBA代码和命名范围中的相对范围

Question

我有下面的公式Range("D3" , "D" & Total_Rows) = "=sum(A1:A10)"如果我在D之前插入一列，那么这个公式现在放错了列。有人告诉我要为D列使用命名范围，但对于这种类型的代码，我不知道如何合并命名范围，因为在一个实例中，我需要它引用单个单元格，而在另一个实例中将它引用列。

Answer 1

我不知道如何合并命名范围，因为在一个实例中，我需要它引用单个单元格，而在另一个实例中将它引用列。

您可以尝试使用Name的范围属性，然后可以使用普通的范围方法，如.Resize或.Offset等。

Sub Test()
'Assume there is a named range in the worksheet 
Dim nm As Name
Dim rngName As Range
Dim rngCell As Range
Dim rngColumn As Range

Set nm = ActiveSheet.Names(1)
Set rngName = Range(nm)
    MsgBox "myName address: " & rngName.Address
Set rngCell = Range(nm).Resize(1, 1)
    MsgBox "the first cell in myRange is " & rngCell.Address
Set rngCell = Range(nm).Resize(1, 1).Offset(3)
    MsgBox "the third cell in myRange is " & rngCell.Address
Set rngColumn = Range(nm).EntireColumn
    MsgBox "the column of myRange is " & rngColumn.Address
'Now insert a column in front of D
rngName.Insert
'Then view the addresses again, see that they have changed
Set rngName = Range(nm)
    MsgBox "myName address: " & rngName.Address
Set rngCell = Range(nm).Resize(1, 1)
    MsgBox "the first cell in myRange is " & rngCell.Address
Set rngCell = Range(nm).Resize(1, 1).Offset(3)
    MsgBox "the third cell in myRange is " & rngCell.Address
Set rngColumn = Range(nm).EntireColumn
    MsgBox "the column of myRange is " & rngColumn.Address

End Sub