Reference to range using embeded variable

Question

I'm having an issue with referring to multiple ranges using for loop. I created a set of ranges using standardized names like range_[number]. Unfortunately when i try to refer to them I constantly get errors or empty ranges.

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

Any ideas how to solve this?

are you using Named Range ? or you are trying to refere to range_1 = ActiveWorkbook.Sheets("Countries").Columns(5).Cells ? — Shai Rado
– Shai Rado, Commented Mar 30, 2017 at 13:56
Welcome to StackOverflow! It would help others answer you if you included the data and the exact error messages you get. Please see stackoverflow.com/help/how-to-ask for guidance about asking questions. — betseyb
– betseyb, Commented Mar 30, 2017 at 14:10

user3598756 · Accepted Answer · 2017-03-30 13:59:19Z

3

you can't refer like you're trying to do

better use an array of ranges

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)

answered Mar 30, 2017 at 13:59

user3598756

29.5k4 gold badges20 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Shai Rado Over a year ago

I was going to press enter with the exact same code, you beat me to it :)

user3598756 Over a year ago

@ShaiRado, this time!

Collectives™ on Stack Overflow

Reference to range using embeded variable

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related