Pass Ranges To Dictionary and Resize Ranges

[rkaczano]

I want to load some ranges into a dictionary and then resize the ranges.

I was originally passing the rng.address to Dictionary but realized that I needed access to the Resize method of the range to resize it. So I switched to adding the range itself.

I am getting a runtime error 424 Object Required in the For Each loop. How do I loop through the actual range variables that I have added to the Dictionary to access and edit their cell addresses?

Sub TEST()
Dim rnglblcoll as Dictionary
Dim rng as Range

Set rnglblcoll = New Scripting.Dictionary

Set rng = Range("NamedRange1")
rnglblcoll.Add item:=rng, Key:="Label1"
Set rng = Nothing
Set rng = Range("NamedRange2")
rnglblcoll.Add item:=rng, Key:="Label2"
Set rng = Nothing
Set rng = Range("NamedRange3")
rnglblcoll.Add item:=rng, Key:="Label3"

'Update Address in Dictionary
For Each rng In rnglblcoll
    rng = rng.Address.Resize(1, 1)
Next

End Sub

 

[Fluff]

Why not just pass the correct range to start with

Sub TEST()
Dim rnglblcoll As Dictionary


Set rnglblcoll = New Scripting.Dictionary


rnglblcoll.Add Range("NamedRange1").Resize(1, 1), "Lable1"
rnglblcoll.Add Range("NamedRange2").Resize(1, 1), "Label2"
rnglblcoll.Add Range("NamedRange3").Resize(1, 1), "Label3"



End Sub

 

[rkaczano]

Thanks.

Quick question, if I wanted to do this in a loop after-the-fact, is it possible to access the range properties and methods?

The reason I ask is I have a large number of ranges to add and I want to avoid hard-coding the variables in the Resize method (i.e Resize(1,1),Resize(2,1),Resize(3,1) . If I put it in a loop I can use a counter variable (i.e. Resize.cells(Counter,1))

Thanks

 

[Fluff]

Depends on what you are trying to do, but you could use something like

Sub TEST()
Dim rnglblcoll As Dictionary
Dim i As Long

Set rnglblcoll = New Scripting.Dictionary


rnglblcoll.Add Range("NamedRange1"), "Lable1"
rnglblcoll.Add Range("NamedRange2"), "Label2"
rnglblcoll.Add Range("NamedRange3"), "Label3"
For i = 1 To 10
   Debug.Print rnglblcoll.Keys(1).Cells(i, 1).Address, rnglblcoll.Keys(1).Resize(i, 1).Address
Next i

End Sub

 

[rkaczano]

I am actually trying to redefine the range of the named address so that I can paste it dynamically in a spreadsheet that has changing rows.

So I will eventually be dong this:

Range("NamedRange1") = DATA from an Array

What I am doing upfront is putting this range and a few others with their existing cell addresses into a dictionary. I then want to loop through the collection and change the addresses based on some criteria. When it comes time to printing, I want to loop through that same collection and access the redefined addresses as the print.

Thanks

 

[Fluff]

Wouldn't it be easier to just delete the ranges, paste the new data in & then recreate the named ranges?