MAX IF Formula in VBA

[prajul89]

I have this formula in Excel which works perfectly
{=MAX(IF(A:A=K1,J:J))}

Where it finds out the maximum value in J column for every K1-value in A column

But I want to Use this formula in Macro.

Of course I can write a lengthy Macro which would Loop and get the result,
but I want a one line code for the above formula.
Is it possible???

 

[taurean]

Suppose you want to enter this formula in range B1 then in VBA:

Range("B1").FormulaArray = "=MAX(IF(A:A=K1,J:J))"

 

[prajul89]

Thanks for The quick Reply.
This is what I was looking for!!!

 

[prajul89]

Well Actually I want to get the result in the macro itself! I don't want to use a cell in it. Is there some code for that like application.worksheetsfunction or something???

 

[taurean]

Maybe I did not understand:

Here you pass formula to a cell then get the result assigned.

Public Sub Test()
Dim myVar As Long
Range("B1").FormulaArray = "=MAX(IF(A:A=K1,J:J))"
myVar = Range("B1").Value
Range("B1").Value = ""
End Sub

Or do it within VBA like:

Public Sub Test2()
Dim myVar As Long
myVar = Evaluate("=MAX(IF(A:A=K1,J:J))")
End Sub

 

[prajul89]

Test2 was what I was looking for.
Super Thanks to you!!!!!

 

[hasan4it]

its nice helping code snippet you shared here!

I want to ask something more about

how can I pass a VBA form value to this formula string?

 

[taurean]

its nice helping code snippet you shared here!

I want to ask something more about

how can I pass a VBA form value to this formula string?

Welcome to MrExcel.

Maybe you should've started a new thread which would have given quicker response.

You could write something like this (untested)

Bold part you will have to change to suit.

Dim myVar As Long 
myVar = Evaluate("=MAX(IF(A:A=" & uf1.tbVariable.Value & ",J:J))")

 

[wilsonw]

Maybe I did not understand:

Here you pass formula to a cell then get the result assigned.

Public Sub Test()
Dim myVar As Long
Range("B1").FormulaArray = "=MAX(IF(A:A=K1,J:J))"
myVar = Range("B1").Value
Range("B1").Value = ""
End Sub

Or do it within VBA like:

Public Sub Test2()
Dim myVar As Long
myVar = Evaluate("=MAX(IF(A:A=K1,J:J))")
End Sub

Is there anyway to copy the result for above VBA to value with code? (additional code for above)

 

[endexci]

BayExcel'e hoş geldiniz.

Belki de daha hızlı tepki verecek yeni bir konu başlatmalıydın.

Böyle bir şey yazabilirsiniz (test edilmemiş)

Kalın kısmı uygun şekilde değiştirmeniz gerekecektir.

Dim myVar Uzun
myVar = Değerlendir("=MAKS(EĞER(A:A=" ve [B]uf1.tbVariable.Value[/B] & ",J:J)")

Bilgi için teşekkürler.