Pass Ranges To Dictionary and Resize Ranges

rkaczano

Board Regular
Joined
Jul 24, 2013
Messages
72
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?

VBA Code:
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Why not just pass the correct range to start with
VBA Code:
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

Board Regular
Joined
Jul 24, 2013
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Depends on what you are trying to do, but you could use something like
VBA Code:
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

Board Regular
Joined
Jul 24, 2013
Messages
72
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
47,887
Office Version
  1. 365
Platform
  1. Windows
Wouldn't it be easier to just delete the ranges, paste the new data in & then recreate the named ranges?
 

Watch MrExcel Video

Forum statistics

Threads
1,112,784
Messages
5,542,500
Members
410,559
Latest member
jordansmith6532
Top