Array <--> Range Error

[Guest]

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

 

[Tom Ogilvy]

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)

 

[Guest]

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

 

[Guest]

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