• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

assigning range values to an array

dan_l

dan_l

Active Member
This is probably me being new to arrays:

Code: 
Dim MyArray(1 To 1000, 2) As Long
Dim rSourceRange As Range
Dim rTargetRange As Range
 
Set rSourceRange = Range("B1:b1000")
Set rTargetRange = Range("g1:g1000")
 
MyArray = Range("b1:b1000")



So ideally I want to set a range, have the array store the range, do a bunch of stuff with it and output it elsewhere. I've been googling around a bit and found this:

http://www.cpearson.com/excel/ArraysAndRanges.aspx


But I'm still not getting where the above is going wrong.
 
You've mentioned 2 columns in your dimensioned string array. Not sure why you need to, if you only need to pass one column.

Anyway, here's an example of how you can pass values from a range to an array

Code: 
Sub SMC()
 
Dim varSourceValues as Variant
 
varSourceValues = Range("B1:B1000").Value
 
End Sub
 
Yes. I need 2. So, this is something of a prototype for a project I'm working though:

I've got like eleventy gazillion rows of cumulative data. Like

Month 1: 10
Month 2: 20
Month 3: 30
Month 4: 40

So---each month I added 10. The ultimate objective is to take those values, and in the second 'column' in the array have that be the value of array(index) = array(index) - array(index - 1). Final output will be both. There's too much data for me to 'just trust', so I want to want to be able to sanity check it.



Incidentally: I can loop through the cells just fine and do what I want to do, I just want to figure out how to do it a bit cleaner.


Why is "variant" preferred?
 
Hi Dan ,

When you assign the values in a range to an array , the array is dimensioned according to the range ; which is why the declaration is not of an array but of a variant.

Thus if you assign a single column range to an array , the array has 2 columns where the data is in the first column ; thus to refer to the first element of the array named vararray , you would use vararray(1,1) , to refer to the second element you would use vararray(2,1) and so on.

If you assign a 10 column range to an array , the array has 11 columns where the data is in the first 10 columns ; thus to refer to the first row , first column element of the array named vararray , you would use vararray(1,1) , to refer to the first row , second column element you would use vararray(1,2) and so on.

To make the array multidimensional , assign the appropriate range to it.

Of course , by multidimensional do you mean more than 2 dimensions ?

Narayan
 
Hi Narayan,

Just curious why you say that there will always be 1 column 'more' than that in the range? If the passed range has only 1 column, I think I've seen the array also having just one column. I probably am missing something, so could you let me know.
 
Hi Sam ,

I was wrong in saying that the array will always have one column more than the range ; this is true only of single-column ranges ; in case the range has 10 columns , the array will also have 10 columns.

Narayan
 
emmm.....:
Code: 
Sub nomorcumu1()
Dim rTargetRange As Range
Dim rSourceRange As Range
Dim varSourceValues() As Variant
Dim iPH As Integer
 
Set rSourceRange = Range("b1:b1000")
Set rTargetRange = Range("d1:e1000")
 
varSourceValues = rSourceRange.Value
 
For iPH = 1 To UBound(varSourceValues)
    If iPH = 1 Then
        varSourceValues(iPH, 2) = varSourceValues(iPH, 1)
    Else
        varSourceValues(iPH, 2) = (varSourceValues(iPH, 1) - varSourceValues(iPH - 1, 1))
    End If
Next iPH
 
 
rTargetRange.Value = varSourceValues



that throws subscript out of range on:

varSourceValues(iPH, 2) = varSourceValues(iPH, 1)



Although if I put a little bug finder inside the loop:

Debug.Print varSourceValues(iPH, 1)

The compiler passes over that.


So that cpearson article says:

"A 2 dimensional array is created even if the worksheet data is in a single row or a single column "



So based on that, I should theoretically have an extra dimension available. But I can't seem to get at it.


Just on the fly I added this before the loop:
ReDim Preserve varSourceValues(1 To UBound(varSourceValues), 1 To 2)


So, re scoping the array explicitly seems to make this thing function as expected. I wonder if, in practice, that's not a better way to do things anyway.
 
I guess there's some confusion about 2 dimensions. When an array is assigned by passing values from a range (whether M x 1 or 1 x N), it will always be two dimensional, which means that the array will have column(s) and row(s). That doesn't mean there will be more than 1 column if it is a single column range, OR more than 1 row if it is a single row range. Even if there are a thousand rows or columns, the array will still be 2 dimensional. I hope that's clear.

And in your statement
Code: 
varSourceValues(iPH, 2) = varSourceValues(iPH, 1)
there's no second column, and it is bound to throw an error.

EDIT: The only reason why varSourceValues(iPH, 2) works is because you re-dimensioned it to HAVE 2 columns.
 
Ok one more stupid question.

If I want to resize the array to add new values beyond the initial transfer: how would I do so?
 
Hi, dan_l!

Assuming that you yet have the array dimensioned and filled up to index X and that you intend to add Y values in the positions X+1 to X+Y try this:
Code: 
Redim Preserve vArray(X+Y)
For I=1 to Y
vArray(X+I)=<something>
Next i

Regards!
 
You must log in or register to reply here.
Back
Top