vba - 使用嵌入变量引用范围

Question

我在使用 for 循环引用多个范围时遇到问题。
我使用 range_[number] 等标准化名称创建了一组范围。
不幸的是，当我尝试引用它们时，我不断收到错误或空范围。

Sub add_new_country()

'count initial number of countries
n = WorksheetFunction.CountA(Range("countries_list"))
'name of last country
last_country = Range("countries_list").Cells(n).Value

range_1 = ActiveWorkbook.Sheets("Countries").Columns(5).Cells
range_2 = ActiveWorkbook.Sheets("Operations").Columns(2).Cells

On Error Resume Next

For i = 1 To 2
    Set active_range = Range("range_" & i)
    For Each c In active_range
        If c.Value = last_country Then
            c.Offset(1).EntireRow.Insert
            c.EntireRow.Cells.Copy
            c.Offset(1).EntireRow.Cells.PasteSpecial Paste:=xlPasteFormats
            c.Offset(1).EntireRow.Cells.PasteSpecial Paste:=xlPasteFormulas
            c.Offset(1).EntireRow.SpecialCells(xlCellTypeConstants).ClearContents
        End If
    Next c
Next i

End Sub

任何想法如何解决这个问题？

Answer 1

你不能像你想做的那样推荐

更好地使用范围数组

Dim ranges(1 To 2) As Range
Set ranges(1) = ActiveWorkbook.Sheets("Countries").Columns(5).Cells
Set ranges(2) = ActiveWorkbook.Sheets("Operations").Columns(2).Cells

On Error Resume Next

For i = 1 To 2
    Set active_range = ranges(i)