Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
I have a two dimensional array. I have tried to sum the second column of
it. When I test I get a sum of 0 but when I test the individual values I clearly get numbers that are greater than 0 and should then Max to the highest of those numbers. What I am I doing wrong? Public UnitOfferArray() As Variant Dim ArrayMax As Variant ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 9)) ?ArrayMax 0 ?UnitOfferArray(1, 9) 49.2126 ?UnitOfferArray(2, 9) 39.7223 ?UnitOfferArray(3, 9) 28.0002 ?UnitOfferArray(4, 9) 27.943 ?UnitOfferArray(5, 9) 11.2936 ?UnitOfferArray(6, 9) 7.6502 ?UnitOfferArray(7, 9) 4.6001 ?UnitOfferArray(8, 9) 1.7726 ?UnitOfferArray(9, 9) 1.2268 ?UnitOfferArray(10, 9) 1.1163 ?UnitOfferArray(11, 9) 0 ?UnitOfferArray(12, 9) 0 ?UnitOfferArray(13, 9) 0 --- Message posted from http://www.ExcelForum.com/ |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
Hi
Try ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 10)) The data is in the 10th column of tjhe array UnitOfferArray. Remember that the array starts from 0. Tony ----- ExcelMonkey wrote: ----- I have a two dimensional array. I have tried to sum the second column of it. When I test I get a sum of 0 but when I test the individual values I clearly get numbers that are greater than 0 and should then Max to the highest of those numbers. What I am I doing wrong? Public UnitOfferArray() As Variant Dim ArrayMax As Variant ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 9)) ?ArrayMax 0 ?UnitOfferArray(1, 9) 49.2126 ?UnitOfferArray(2, 9) 39.7223 ?UnitOfferArray(3, 9) 28.0002 ?UnitOfferArray(4, 9) 27.943 ?UnitOfferArray(5, 9) 11.2936 ?UnitOfferArray(6, 9) 7.6502 ?UnitOfferArray(7, 9) 4.6001 ?UnitOfferArray(8, 9) 1.7726 ?UnitOfferArray(9, 9) 1.2268 ?UnitOfferArray(10, 9) 1.1163 ?UnitOfferArray(11, 9) 0 ?UnitOfferArray(12, 9) 0 ?UnitOfferArray(13, 9) 0 --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
But I used Option Base 1 before my variable declarations.
--- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
Ok
When I use option base 1, then it works for me. I tested with the following Sub eee() Dim arr2(4, 9) arr = Array(39.7223, 49.2126, 28.002, 27.943) For i = 1 To 4 arr2(i, 9) = arr(i) Next i MsgBox WorksheetFunction.Max(WorksheetFunction.Index(arr2 , 0, 9)) End Sub ----- ExcelMonkey wrote: ----- But I used Option Base 1 before my variable declarations. --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
Sub Tester1()
Dim UnitOfferArray(1 To 13, 1 To 9) As Double Dim var As Double UnitOfferArray(1, 9) = 49.2126 UnitOfferArray(2, 9) = 39.7223 UnitOfferArray(3, 9) = 28.0002 UnitOfferArray(4, 9) = 27.943 UnitOfferArray(5, 9) = 11.2936 UnitOfferArray(6, 9) = 7.6502 UnitOfferArray(7, 9) = 4.6001 UnitOfferArray(8, 9) = 1.7726 UnitOfferArray(9, 9) = 1.2268 UnitOfferArray(10, 9) = 1.1163 UnitOfferArray(11, 9) = 0 UnitOfferArray(12, 9) = 0 UnitOfferArray(13, 9) = 0 var = Application.Sum(Application.Index(UnitOfferArray, 0, 9)) Debug.Print var End Sub produced: 172.5377 if you declare the lower bound, then there is no doubt what it is. -- Regards, Tom Ogilvy "ExcelMonkey " wrote in message ... I have a two dimensional array. I have tried to sum the second column of it. When I test I get a sum of 0 but when I test the individual values I clearly get numbers that are greater than 0 and should then Max to the highest of those numbers. What I am I doing wrong? Public UnitOfferArray() As Variant Dim ArrayMax As Variant ArrayMax = Application.Max(Application.Index(UnitOfferArray, 0, 9)) ?ArrayMax 0 ?UnitOfferArray(1, 9) 49.2126 ?UnitOfferArray(2, 9) 39.7223 ?UnitOfferArray(3, 9) 28.0002 ?UnitOfferArray(4, 9) 27.943 ?UnitOfferArray(5, 9) 11.2936 ?UnitOfferArray(6, 9) 7.6502 ?UnitOfferArray(7, 9) 4.6001 ?UnitOfferArray(8, 9) 1.7726 ?UnitOfferArray(9, 9) 1.2268 ?UnitOfferArray(10, 9) 1.1163 ?UnitOfferArray(11, 9) 0 ?UnitOfferArray(12, 9) 0 ?UnitOfferArray(13, 9) 0 --- Message posted from http://www.ExcelForum.com/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Max of VBA Array
So to expand on this further, lets now way that my array has 5
dimensions. I fill the array with values within 5 imbedded For Next Loops. Assume I use the rnd() function to fill them for simplicity. Once I have filled all the rows and columns in the first two dimensions (i.e. rows and columns) I want to sum all the data in column 9. The following is not working. It is giving me a Type Mismatch Error for ArrayMax. Private Sub Other() Dim A As Integer Dim B As Integer Dim C As Integer Dim D As Integer Dim E As Integer Dim Array1() As Variant Dim ArrayMax As Variant ReDim Array1(1 To 9, 1 To 13, 1 To 5, 1 To 5, 1 To 5) For A = 1 To 5 For B = 1 To 5 For C = 1 To 5 For D = 1 To 13 For E = 1 To 9 Array1(E, D, C, B, A) = Rnd() Next E Next D ArrayMax = Application.Max(Application.Index(Array1, 0, 9, 0, 0, 0)) Next C Next B Next A End Sub --- Message posted from http://www.ExcelForum.com/ |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
variant array containing cel adresses convert to actual ranges-array | Excel Programming |