Programming Array Formulas in VBA - Can they be intermediate results in RAM?

[Bill Hertzing]

I'd like to be able to set a cell value, using the results of an array
formula as an intermediate step. For example, Column 1 of my data is a
timestamp. Column 5 is series of numbers. I want to place, on sheet 2,
the maximum value of column 5, and the date/time that it occurred.

I've actually got many columns to perform this on, and each column has
a named range that covers the column just from row 2 to the last row
with data. In the example below, I'm interested in the range
InBytesCur

The following from John Walkenbach's 2003 formula book suggests this
array formula for finding the row of the max
=MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur), ""))

1) If I use this code to get the row of the max:
Dim TC As Range
Set TC = Range("Sheet2!a2")
TC.FormulaArray =
"=MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur), ""))"

I get the error that it can't set the FormulaArray property on the
range object. I'm getting that error a lot, trying to learn to program
array formulas into my worksheets via macros. I've discovered the
messages in this group that explain VBA won't accept an FormulaArray
string of more than 256 chars, but that doesn't explain why the above
example won't run.

But in the larger picture, I'd prefer to figure out how to use the
Array Formula directly, without having to place it's results in a
cell. Can an Array Formula operate in memory, without having to place
it's results in a cell? If so, I can find the date/time value I need
using RC offset on the data sheet (Row X, Column 1, where X comes from
the Array Formula).

Thanks in advance for any pointers!

 

[Rob van Gelder]

You nearly got it - just a bit of trouble with the quote marks.
Instead of "", try """"


As for the larger picture, something like this might work:

Sub test()
Dim rng As Range

Set rng = Range("InBytesCur")
With WorksheetFunction
Set rng = rng(.Match(.Max(rng), rng, 0))
End With

MsgBox rng.Address & " = " & rng.Value
End Sub

 

[Bob Phillips]

Bill,

One way of getting the result in VBA is as Rob shows, but with nested
functions this can get messy. Here is an alternative using your formula

myResult = Evaluate("=MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur),
""""))")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tom Ogilvy]

The second argument is superfluous (without this, it returns false which
works fine)

=MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur)))

Should work fine and eliminates the double quote problem.

Dim TC As Range
Set TC = Range("Sheet2!a2")
TC.FormulaArray = _
"=MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur)))"

or

res = Evaluate("MIN(IF(InBytesCur=MAX(InBytesCur),ROW(InBytesCur)))")

But Rob has shown you how to get the row without using an array formula
either in the worksheet or in code.

 

[Bill Hertzing]

Thank you all for your excellent suggestions!