Array <-- Range Error
 

In response to an answer to my earlier question, I'm trying to load the
contents of a range into an array, page through it and make some changes, and
then dump the changes back into the range. However, I seem to get a
"Subscript out of range" error whenever I try to actually change anything in
the array. Below is the relevant code:

Dim arrCols

Set objR = objWS.Range(objWS.Cells(2, J), objWS.Cells(lngCurrentRow, J))

arrCols = objR.Value

MsgBox UBound(arrCols) ' In my test ~ 8,000

arrCols(1) = 555555 ' ERROR - tried with "5555555" also

objR.Value = arrCols


--
Hmm...they have the Internet on COMPUTERS now!

Array <-- Range Error
 

arrCols(1,1) = 555555


When you pick up a range from the worksheet, it is 2 dimensional. Since you
picked up a single column, it is

arrCols(1 x n, 1 x 1)

--
Regards,
Tom Ogilvy



"MDW" wrote in message
...
In response to an answer to my earlier question, I'm trying to load the
contents of a range into an array, page through it and make some changes,
and
then dump the changes back into the range. However, I seem to get a
"Subscript out of range" error whenever I try to actually change anything
in
the array. Below is the relevant code:

Dim arrCols

Set objR = objWS.Range(objWS.Cells(2, J), objWS.Cells(lngCurrentRow, J))

arrCols = objR.Value

MsgBox UBound(arrCols) ' In my test ~ 8,000

arrCols(1) = 555555 ' ERROR - tried with "5555555" also

objR.Value = arrCols


--
Hmm...they have the Internet on COMPUTERS now!

Array <-- Range Error
 

Hi,

When matching to a range, the array is in fact a 2-dimension array (except
for single cell range) and doing Ubound(v) means in fact Ubound(v,1) which
only returns the upper bound for the first dimension.
So, try
Ubound(v,1) and Ubound(v,2)
And
arrCols(1,1) = 555555
--
Regards,
Sébastien
<http://www.ondemandanalysis.com


"MDW" wrote:

In response to an answer to my earlier question, I'm trying to load the
contents of a range into an array, page through it and make some changes, and
then dump the changes back into the range. However, I seem to get a
"Subscript out of range" error whenever I try to actually change anything in
the array. Below is the relevant code:

Dim arrCols

Set objR = objWS.Range(objWS.Cells(2, J), objWS.Cells(lngCurrentRow, J))

arrCols = objR.Value

MsgBox UBound(arrCols) ' In my test ~ 8,000

arrCols(1) = 555555 ' ERROR - tried with "5555555" also

objR.Value = arrCols


--
Hmm...they have the Internet on COMPUTERS now!

Array <-- Range Error
 

Hi,
Code needs to be something like this:

Sub Test()

Dim UserRange as Range
Dim v as variant
Dim r as long, c as Integer

Set UserRange=Range("A1:AZ3000") ' Change as needed

v=UserRange

For r = 1 to Ubound(v,1)
For c = 1 to Ubound(v,2)

' Perform operation
v(r,c)="'" & v(r,c) ' Add ' to field
Next c
Next r

UserRange=v

End sub

HTH

"MDW" wrote:

In response to an answer to my earlier question, I'm trying to load the
contents of a range into an array, page through it and make some changes, and
then dump the changes back into the range. However, I seem to get a
"Subscript out of range" error whenever I try to actually change anything in
the array. Below is the relevant code:

Dim arrCols

Set objR = objWS.Range(objWS.Cells(2, J), objWS.Cells(lngCurrentRow, J))

arrCols = objR.Value

MsgBox UBound(arrCols) ' In my test ~ 8,000

arrCols(1) = 555555 ' ERROR - tried with "5555555" also

objR.Value = arrCols


--
Hmm...they have the Internet on COMPUTERS now!