Array <--> Range Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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)
 
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
 
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
 
Back
Top