How do I VBA-ise an Array Function which returns MAX value

[Kriskros07]

Hello,

The following function will work when CTRL-SHIFT-ENTER from a cell in Excel

=MAX(IF((A2:A1655="ProductName"),C2:C1655,0))

where C2:C1655 contains a list of values. i.e. this will return the maximum value from an array (column C) if it meets criteria met in another column
(column A), else returns 0.

However, how can i recreate this sort of thing in VBA?

Kind Regards,
Kris

 

[jc.021286]

Hey there Kris,

Array formulas are possible,
In vba you can use RC styles to create your formulas.

Selection.Formula = "=A1+B1"
Will give you a standard formula

Selection.FormulaArray = "=MAX(IF((R2C1:R1655C1="""ProductName"""),R2C3:R1644C3,0))"

You will need to play around a bit with the """ProductName""" and double quotations to get it to fit right.

I'd suggest playing with the msgbox
MsgBox "=MAX(IF((R2C1:R1655C1="""ProductName"""),R2C3:R1644C3,0))"
to fine tune and get it correctly.

RC formula styles if you place a number not inside [] it will be an absolute reference $A$2. Having it inside [] will make it a relative reference to where the formula currently is. Based on your formula I presume you want these references fixed.

Please let me know if this works for you,
jc

=MAX(IF((A2:A1655="ProductName"),C2:C1655,0))

 

[jim may]

One way....

Sub Foo()

    Range("F1").FormulaArray = _
        "=MAX(IF((A2:A1655=""ProductName""),C2:C1655,0))"
    Range("F1").Value = Range("F1").Value
    
End Sub

 

[Kriskros07]

Thanks for your rapid response its very much appreciated.

Unfortunatley when i try use the MsgBox then all i get returned is the formula in quotes returned and not the returned value

Any ideas?

I'm calling a UDF from a cell and trying to use your code within some If Then logic. The Data i ma referenceing actually exists on another sheet so i have just referenced the cells as normal e.g. 'Sheet1'!E13:E1655 is this likely to be an issue?

MsgBox "=Min(If(('Sheet1'!E13:E1655 = ""ProductType""), 'Sheet1'!N13:N1655, 0))"

 

[Kriskros07]

Hi Jim, I need to use the reurned value for further processing. Is your last line correct or should this be:

Range("F1").Value = Range("F1").FormulaArray

This looks like a useful bit of code then as i need to convert a Variant into a String.

 

[jim may]

I use the .value = .value to remove the formula from the worksheet, leaving only the raw value of the original formula.

 

[jc.021286]

Okay,

So with your UDF,

I believe you are just looking to Evaluate your ArrayFormula.

Your UDF, I presume is simply =MyFormulaName(ProductType) where the product type is what you are having the user input into the formula?

Thus,

Public Function myFormula(ProductType As String)
MsgBox "=Min(If(('Sheet1'!E13:E1655 = """ & ProductType & """), 'Sheet1'!N13:N1655, 0))"
myFormula = Evaluate("=Min(If(('Sheet1'!E13:E1655 = """ & ProductType & """), 'Sheet1'!N13:N1655, 0))")

End Function

Comment out the msgbox to get rid of it.
might need to add a line or two to convert any entry into a string.
Thus, for me w/o any data,
any cell with the formula =myFormula("test")
returns 0 for me as the formula would suggest.

jc

Thanks for your rapid response its very much appreciated.

Unfortunatley when i try use the MsgBox then all i get returned is the formula in quotes returned and not the returned value

Any ideas?

I'm calling a UDF from a cell and trying to use your code within some If Then logic. The Data i ma referenceing actually exists on another sheet so i have just referenced the cells as normal e.g. 'Sheet1'!E13:E1655 is this likely to be an issue?

MsgBox "=Min(If(('Sheet1'!E13:E1655 = ""ProductType""), 'Sheet1'!N13:N1655, 0))"