VBA- Excel formula not working in vba

[Mallesh23]

Hi Team


Need two help here,
First Help,


I am pasting Below excel formula into vba, its not working. it works in excel.

'=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,{"A","B"},0))*ISNUMBER(MATCH($B$2:$B$18,{"X","Y","Z"},0)))
'=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,$D$4:$D$7,0))*ISNUMBER(MATCH($B$2:$B$18,$E$4:$E$7,0)))


Sub TEST()
ActiveSheet.Range("h1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH(range($A$2:$A$18),{""A"",""B""},0))*ISNUMBER(MATCH(range($B$2:$B$18),{""X"",""Y"",""Z""},0)))"
End Sub


Output formula in H1
=SUMPRODUCT(ISNUMBER(MATCH(range($A$2:$A$18),{"A","B"},0))*ISNUMBER(MATCH(range($B$2:$B$18),{"X","Y","Z"},0)))




Second Help,
If I want the result which are not available in the Criteria Array. how to use <> here.





Thanks for your help in Advance!



Regards,
mg

 

[James006]

Hello,

You should test the Evaluate instruction ...

MsgBox Evaluate("your_formula")

Hope this will help

 

[jasonb75]

Sub TEST()
ActiveSheet.Range("h1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH(range($A$2:$A$18),{""A"",""B""},0))*ISNUMBER(MATCH(range($B$2:$B$18),{""X"",""Y"",""Z""},0)))"
End Sub

Your mistake is using Range(...) within the formula, as you're only using vba to enter the formula into the worksheet you need to write it as a formula, not as vba. Apart from that, the rest looks fine.

Changing ISNUMBER to ISERROR should count the <> results.

 

[Mallesh23]

Hi James and Jason,
Thanks for your help, Jason I used your suggested approach its working,

James - Can you please where to add Evaluate in below formula.
ActiveSheet.Range("h1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,{""A"",""B""},0))*ISNUMBER(MATCH($B$2:$B$18,{""X"",""Y"",""Z""},0)))"


Regards,
mg

 

[James006]

Hi again,

Range("H1") =  Evaluate("=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,{""A"",""B""},0))*ISNUMBER(MATCH($B$2:$B$18,{""X"",""Y"",""Z""},0)))")

Hope this will help

 

[Mallesh23]

Hi, James and Jason


Thank you both for your help , One more help if you can,
Below formula its working Perfectely, I want to pass Criteria Dynamically.


Below formula works.
Range("H1") = Evaluate("=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$18,{""A"",""B""},0))*ISNUMBER(MATCH($B$2:$B$18,{""X"",""Y"",""Z""},0)))")


Sub Test
Dim ar1 as variant
Dim ar2 as variant


ar1 = Application.WorksheetFunction.Transpose(Range("c2:c3"))
ar1 = Application.WorksheetFunction.Transpose(Range("d2:d4"))


Getting Type mismatch Error.
Range("H1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$48," & ar1 & ",0))*ISNUMBER(MATCH($B$2:$B$48," & ar2 & ",0)))"


End sub


Regards,
mg

 

[James006]

Hi,

Which formula are you referring to ...?

 

[Mallesh23]

Hi James,

I tried both the formula, But for below formula Can you suggest where it is going wrong. Thanks.

Sub Test
Dim ar1 as variant
Dim ar2 as variant

ar1 = Application.WorksheetFunction.Transpose(Range("c2:c3"))
ar1 = Application.WorksheetFunction.Transpose(Range("d2:d4"))

Getting Type mismatch Error.
Range("H1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$48," & ar1 & ",0))*ISNUMBER(MATCH($B$2:$B$48," & ar2 & ",0)))"

End sub

Thanks
mg

 

[jasonb75]

I don't think that a vba array is valid as an array constant, try dropping the range address into the formula instead.

Dim rng1 As Range, rng2 As Range
Set rng1 = Range("C2:D3")
Set rng2 = Range("D2:D4")
Range("H1").Formula = "=SUMPRODUCT(ISNUMBER(MATCH($A$2:$A$48," & rng1.Address & ",0))*ISNUMBER(MATCH($B$2:$B$48," & rng2.Address & ",0)))"

 

[Mallesh23]

Hi Jason,


Thanks Once again for your help, learned lot of new things.
One more last and final Question , Adding one more Criteria to previous formula.


Column C Contains Numbers starting 0 to 100. I want Count Greater than 35.


What I needs add here to Make dynamic and adjust with previous two criteria.


*ISNUMBER(MATCH($C$1:$C$36,$G$2,0))))"


Range("o2").Formula = "=SUMPRODUCT((ISNUMBER(MATCH($A$1:$A$36,$E$2:$E$4,0)) _
*ISNUMBER(MATCH($B$1:$B$36,$F$2:$F$4,0)) _
*ISNUMBER(MATCH($C$1:$C$36,$G$2,0))))"

Thanks for your help in advance !!


Regards,
mg