i want to store an unknown number variables in a 2 dimensional array and can't
figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

When you use the Preserve keyword, you can only change the size of the last
dimension. This is discussed in the help files for the ReDim statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

ok, i see now. then how would i do this:

if i set arr = range("A1:B4") for example and then use the watch window to view
the array, i get this:
this is just an example, i'm not doing this
arr(1,1) = "item"
arr(1,2) = "desc"
arr(1,3) = "cases"
arr(1,4) = "weight"


in code i need to populate the array with values, but i don't know how many
elements i need because i'm in the process of building it in the array:
arr(2,1) = "item"
arr(2,2) = "desc"
arr(2,3) = "cases"
arr(2,4) = "weight

arr(3,1) = "item"
arr(3,2) = "desc"
arr(3,3) = "cases"
arr(4,4) = "weight

and so on
arr(n,1) = "item"
arr(n,2) = "desc"
arr(n,3) = "cases"
arr(n,4) = "weight
--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
When you use the Preserve keyword, you can only change the size of the last
dimension. This is discussed in the help files for the ReDim statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

got it worked out by redimming after i know how many elements i need.

thanks

--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
When you use the Preserve keyword, you can only change the size of the last
dimension. This is discussed in the help files for the ReDim statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

You may have another problem. How is arr declared? Because you used the Set
keyword in your example, I'm guessing as a Variant like this...

Dim arr As Variant

because if you had declared it as a dynamic array (that is, with an empty
set of parentheses) like this...

Dim arr() As Variant

your Set statement would have generated an error. I know it is a popular
belief that doing this...

Set arr = Range("A1:B4")

initializes arr as an array. I don't think that is actually what is
happening. What I believe happens is the Range Collection is assigned to arr
and you just end up using its name as an alias for the Range Collection
itself. The Item method is the default method for a Collection Object which
means it does not have to be explicitly specified. Consider this with the
original Range Collection...

Debug.Print Range("A1:B4").Item(1,2)

it will print out whatever is in B1 (first row, second column). Now consider
this where we do not explicitly specify the default Item method...

Debug.Print Range("A1:B4")(1,2)

it will print out the exact same cell value. Now consider what happens when
you do this...

Set arr = Range("A1:B4")

I think arr ends up assigning a reference the Range Collection (an object)
to the Variant arr; but, a Variant can store practically anything... in this
case, it ends up storing a Collection Object, specifically, the Range
Collection. Because of that, you can reference its elements using either
this syntax...

Debug.Print arr.Item(1,2)

or this syntax where the default Item method is not specifically
specified....

Debug.Print arr(1,2)

It is this latter syntax which gets exclusively used and is almost always
(incorrectly I believe) spoken of as an array. Now, that was a long winded
way of saying I don't think you can assign the Range to your Variant
variable and then ReDim Preserve it later on because I don't think it ever
was an array to begin with. What I think you will have to do is Dim an
actual array, initially ReDim it with the elements reverse (if you think
row, column, then make it column, row)... that way you will be able to ReDim
Preserve and do so by changing the last element (the only one you are
allowed to change).

Rick




"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
ok, i see now. then how would i do this:

if i set arr = range("A1:B4") for example and then use the watch window to
view the array, i get this:
this is just an example, i'm not doing this
arr(1,1) = "item"
arr(1,2) = "desc"
arr(1,3) = "cases"
arr(1,4) = "weight"


in code i need to populate the array with values, but i don't know how
many elements i need because i'm in the process of building it in the
array:
arr(2,1) = "item"
arr(2,2) = "desc"
arr(2,3) = "cases"
arr(2,4) = "weight

arr(3,1) = "item"
arr(3,2) = "desc"
arr(3,3) = "cases"
arr(4,4) = "weight

and so on
arr(n,1) = "item"
arr(n,2) = "desc"
arr(n,3) = "cases"
arr(n,4) = "weight
--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
When you use the Preserve keyword, you can only change the size of the
last dimension. This is discussed in the help files for the ReDim
statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

In light of the mini-essay I just posted, I'd be interested in seeing your
code. I'm going to sleep for the night now, so if you post it, I'll look at
it when I get up.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
got it worked out by redimming after i know how many elements i need.

thanks

--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
When you use the Preserve keyword, you can only change the size of the
last dimension. This is discussed in the help files for the ReDim
statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

Gary,

As mentioned, the variable part of an array should be the last dimension,
not the first.

But you should also be aware that Redim Preserve is a very process hungry
action and should not be overdone. It is better to Redim it before loading

dim arr() as string

redim arr(1 to itemnum.count, 1 to 6)

for n = 1 to itemnum.count
then code to add a values
next

If you don't know how many you will be loading, make a high guess, and then
Redim down to the actual size at the end.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

that's what i ended up doing,

thanks for the explanation

--


Gary


"Bob Phillips" wrote in message
...
Gary,

As mentioned, the variable part of an array should be the last dimension, not
the first.

But you should also be aware that Redim Preserve is a very process hungry
action and should not be overdone. It is better to Redim it before loading

dim arr() as string

redim arr(1 to itemnum.count, 1 to 6)

for n = 1 to itemnum.count
then code to add a values
next

If you don't know how many you will be loading, make a high guess, and then
Redim down to the actual size at the end.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

rick, thanks for the explanation. i'm probably not even going to use the code, i
had already done it another way. i was simply trying alternative methods to gain
the same result. i may need to use something like this in the future so i wanted
to see the difference in the 2 methods and see if either was more efficient.

--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
In light of the mini-essay I just posted, I'd be interested in seeing your
code. I'm going to sleep for the night now, so if you post it, I'll look at it
when I get up.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
got it worked out by redimming after i know how many elements i need.

thanks

--


Gary


"Rick Rothstein (MVP - VB)" wrote in
message ...
When you use the Preserve keyword, you can only change the size of the last
dimension. This is discussed in the help files for the ReDim statement.

Rick


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary

tanks for the info, dana.

--


Gary


"Dana DeLouis" wrote in message
...
If interested, one workaround is to change the thinking for m(record, field)
to m(field, record)

Sub Demo()
Dim m()
Dim Rec '# of Records

Rec = 1
ReDim m(1 To 3, 1 To Rec)
m(1, Rec) = "a"
m(2, Rec) = "b"
m(3, Rec) = "c"

' Add a new record
Rec = Rec + 1
ReDim Preserve m(1 To 3, 1 To Rec)
m(1, Rec) = "d"
m(2, Rec) = "e"
m(3, Rec) = "f"

Rec = Rec + 1
ReDim Preserve m(1 To 3, 1 To Rec)
m(1, Rec) = "g"
m(2, Rec) = "h"
m(3, Rec) = "i"

'When finished adding...
m = WorksheetFunction.Transpose(m)
[A1].Resize(3, 3) = m
End Sub

But as was mentioned, it's not efficient when the array gets large.
Another option is the Dictionary Object.

--
Dana DeLouis


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
that's what i ended up doing,

thanks for the explanation

--


Gary


"Bob Phillips" wrote in message
...
Gary,

As mentioned, the variable part of an array should be the last dimension,
not
the first.

But you should also be aware that Redim Preserve is a very process hungry
action and should not be overdone. It is better to Redim it before loading

dim arr() as string

redim arr(1 to itemnum.count, 1 to 6)

for n = 1 to itemnum.count
then code to add a values
next

If you don't know how many you will be loading, make a high guess, and then
Redim down to the actual size at the end.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
i want to store an unknown number variables in a 2 dimensional array and
can't figure out how to use redim preserve

here's what i tried

dim arr() as string

redim arr(1 to 1, 1 to 6)

for n = 1 to itemnum.count
ReDim Preserve arr(1 To n, 1 To 6)
then code to add a values
next

on the 2nd loop i always get an error
can someone enlighten me please?
--


Gary