Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays
Greetings All,
I am having difficulty understanding the Application.Worksheetfunction.expression concept, for Arrays. What I am trying to do is bascially the following: vArr = Range("A1:G20") 'Read in numbers in array, vArr. 1) Get the maximum value that is in vArr in column 3 between the rows 5 and 15. 2) Get the minimum value that is in vArr in row 3 between columns 3 and 5. 3) Once the maximum value and minimum known, to tell me the cell location of those values within vArr. I can do the above in a worksheet, but I would like to do it in memory. Thanks in advance. RichardG |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays
Don't see why you need varr
Application.MAX(Rang("C3:E3")) Application.MIN(Rang("C3:E3")) Range("C3").Offset(0, Application.Match(Application.Max(Range("C3:E3")), Range("C3:E3"), 0) - 1).Address etc. -- HTH Bob Phillips wrote in message oups.com... Greetings All, I am having difficulty understanding the Application.Worksheetfunction.expression concept, for Arrays. What I am trying to do is bascially the following: vArr = Range("A1:G20") 'Read in numbers in array, vArr. 1) Get the maximum value that is in vArr in column 3 between the rows 5 and 15. 2) Get the minimum value that is in vArr in row 3 between columns 3 and 5. 3) Once the maximum value and minimum known, to tell me the cell location of those values within vArr. I can do the above in a worksheet, but I would like to do it in memory. Thanks in advance. RichardG |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays
Hers's one way:
Sub test1() Dim vArr, vArrmax, vArrmin Dim iMax As Long, iMin As Long Dim iMaxAddr As String, iMinAddr As String vArr = Range("A1:G20") vArrmax = Range("C5:C15") vArrmin = Range("C3:E3") iMax = Application.Max(vArrmax) iMaxAddr = "C" & Application.Match(iMax, vArrmax, 0) + 4 iMin = Application.Min(vArrmin) iMinAddr = Chr(Application.Match(iMin, vArrmin, 0) + 2 + 64) & 3 Debug.Print iMax, iMaxAddr Debug.Print iMin, iMinAddr End Sub Bob Phillips wrote: Don't see why you need varr Application.MAX(Rang("C3:E3")) Should be Application.Max(Range("C5:C15")) Application.MIN(Rang("C3:E3")) Range("C3").Offset(0, Application.Match(Application.Max(Range("C3:E3")), Range("C3:E3"), 0) - 1).Address etc. Alan Beban |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays
Thanks Alan,
That appears what I am looking for. I was about to reply with the following- "In part and from an earlier posting by Alan Beban, Dec. 18 2001- Application.Max(Index(arr,0,2)) will return the maximum in the second column of arr. Application.Max(Index(arr,3,0)) will return the maximum in the third row of arr. Application.Max(Application.Max(Index(arr,0,2)),Ap plication.Max(Index(arr,0,3))) will return the maximum in the second and third columns of arr." My further comments would have been - since this code is for an entire row or column, I thought there would be something that can do a sub-range within the array, without initially specifying the sub-range first, as you did in your exellent example: vArrmax = Range("C5:C15") , vArrmin = Range("C3:E3"). My thought - is there some combination like Offset and Index, that would recreat the sub-range of interest and then Max/Min function can perform its operation on that sub-range. If there isn't, I am happy with your example. It is a lot closer, than what I have be struggling with. Many, many thanks you and everyone. RichardG |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Arrays
Thanks Alan,
My initial project was to do everything in an array instead of on a worksheet. The thought was less time was spent moving around in an array verses on a worksheet. However, I have found that performing the Max calculations and updating is faster on the worksheet than trying to do the same things in an array. So depending on what is being performed, the worksheet functions can be faster than trying to similar calculations in an array. Also, I interchange sub-range and sub-array to mean the same, which I should not. Whichever term I use, I am referencing that I am working on a smaller region within main range. With your feedback, I now have a better understanding of the Resize, sub-range and sub-array. In all, I have gained a little more experience on when to use an array and worksheet regarding calculations. Many thanks. RichardG |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Avg Arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Use of arrays | Excel Worksheet Functions | |||
Arrays | Excel Discussion (Misc queries) | |||
Need help with arrays (I think) | Excel Worksheet Functions |