Finding the value of the first / last elements in an array

[JR_Chicago]

I have 10 one-dimensional arrays of numerical values, each of which contains 10 elements...

I have declared the arrays as Variants and have assigned ranges of cells to the arrays as follows:

array1 = Worksheets("Sheet1").Range("B2:K2").Value
array2 = Worksheets("Sheet1").Range("B3:K3").Value

etc etc...

I want to find the values for the first and last elements in these arrays but am having trouble writing the code to do so.

I read about the LBound and UBound functions but when I use those the value that is returned is "1" in both cases.

I've tried:

array1First = array1(1)
array1Last = array1(10)

Thinking that in an array with 10 elements, that such a convention would work but I just get an error message...

Also, I am trying to construct a one-dimensional array with 8 elements, each of which is a numerical value.

For this array, there is no range within the worksheet where the values are contained which I can assign to the array, rather, the array will contain the first & last elements in another array, as well as the mean, median, mode, etc of another array. I've written:

Dim statArray1(8) As Double

statArray1(1) = array1Mean
statArray1(2) = array1Median
statArray1(3) = array1Mode
statArray1(4) = array1First
statArray1(5) = array1Max
statArray1(6) = array1Min
statArray1(7) = array1Last
statArray1(8) = array1Range

In hopes of assigning array1Mean, array1Median, etc to the 1st, 2nd, etc elements in the array, however when I print statArray1 to my worksheet, zeros are populated where I'd hoped to see the stats I assigned to the array. I know the stats are being calculated properly so it must be the way I am assigning them to the array.

Any suggestions would be much appreciated!

Thanks a million!

 

[VoG]

Reading arrays from the worksheet generates 2-dimensional arrays.

Try

array1First = array1(1, 1)
array1Last = array1(10, 1)

 

[JR_Chicago]

I get a "Subscript out of range" error when I make that change... The original array is set-up as follows:

Dim array1 As Variant

array1 = Worksheets("Sheet1").Range("B2:K2").Value

Does the fact that I didn't set up the array like:

Dim array1(1 To 10) as Variant

Make it impossible to be to find an element in the array using the index approach you used?

 

[JR_Chicago]

Also, I think I ammended my original post (added another question) while you were in the middle of responding to my first question... Any pointers on the 2nd question would be much appreciated as well... Thanks Pete.

 

[pgc01]

Hi

No, there's just a typo in Peter's statement for the end value. Your array is horizontal, has only 1 row and 10 columns, so the second statement should be:

array1Last = array1(1, 10)

 

[JR_Chicago]

Ok that works! haha I was wondering... I was like wait isn't it array(row,column)? not the other way around?!

Anything you can say about my second question!

Thanks!

 

[JR_Chicago]

Actually... I think your helping me with the first question lends to the second question... Sorry, new to writing macros and programming in general... Sometimes you just have to work at it for a while and try different things...

I might be good now

 

[pgc01]

Also, I am trying to construct a one-dimensional array with 8 elements, each of which is a numerical value.

For this array, there is no range within the worksheet where the values are contained which I can assign to the array, rather, the array will contain the first & last elements in another array, as well as the mean, median, mode, etc of another array. I've written:

Dim statArray1(8) As Double

statArray1(1) = array1Mean
statArray1(2) = array1Median
statArray1(3) = array1Mode
statArray1(4) = array1First
statArray1(5) = array1Max
statArray1(6) = array1Min
statArray1(7) = array1Last
statArray1(8) = array1Range

In hopes of assigning array1Mean, array1Median, etc to the 1st, 2nd, etc elements in the array, however when I print statArray1 to my worksheet, zeros are populated where I'd hoped to see the stats I assigned to the array. I know the stats are being calculated properly so it must be the way I am assigning them to the array.

Any suggestions would be much appreciated!

Thanks a million!

Well, first you are declaring an array with 9 elements (0 to 8) but your are only using 8 (1 to 8)

Better to change the dimensioning of the array:

Dim statArray1(1 to 8) As Double

To print the array to the worksheet:

To a horizontal range it's direct:

range("A1:J1").value=statArray

or

Range("A1").resize(1,10).value=statArray

To a vertical array you have to transpose it:

range("A1:A10").value=Application.Transpose(statArray)

or

Range("A1").resize(10,1).value=Application.Transpose(statArray)

 

[JR_Chicago]

Ok now I have:

Dim statArray1(1 To 8) As Double
 
statArray1(1) = array1Mean
statArray1(2) = array1Median
statArray1(3) = array1Mode
statArray1(4) = array1Open
statArray1(5) = array1Max
statArray1(6) = array1Min
statArray1(7) = array1Close
statArray1(8) = array1Range

There are 10 "statArrays" each with 1 row and 8 columns... I was trying to construct a For loop that would loop through each array and input the elements value into the corresponding cell... I realize that this necessitates the use of two loops, but I am unsure as to how to write that... the loop I've written to deal with just one array based on the code above looks as follows:

startRow = 18 'the last row is row 27
startColumn = 2
For i = 1 To 8
Cells(startRow, startColumn + i - 1) = statArray1(i)
Next i

I'm still getting all zeros in the range B18:I18 though?

Suggestions for the a nested loop...? Thanks.

 

[pgc01]

It seems your variables array1Mean, etc., are empty.

When you assign the values, check the values of the variables, ex.:

statArray1(1) = array1Mean
MsgBox array1Mean            ' displays the value of array1Mean

statArray1(2) = array1Median
statArray1(3) = array1Mode

What's the value it displays?