Arrays

[rgeorgerian]

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

 

[Bob Phillips]

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.

 

[Alan Beban]

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

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

 

[rgeorgerian]

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)),Application.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

 

[Alan Beban]

Well, I'm not exactly sure what you are probing for. In particular, I
don't know whether you really mean "sub-range" rather than "sub-array".
If you really mean "sub-range", it does indeed raise the question of
what you are trying to accomplish by using arrays at all rather working
with ranges.

In any event, with A1:G20 assigned to the object variable rng, you can
generate the subarray with something like

vArrMax = rng(3, 5).Resize(11, 1)

[or a sub-range with Set rngMax = rng(3, 5).Resize(11, 1), but I'm not
sure that this is preferable to Set rngMax = Range("C5:C15")]

But the difficulty with the sub-array is that the array indexes are no
longer associated with a range for getting the worksheet addresses.

Alan Beban

 

[rgeorgerian]

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