Need help converting formula to VBA...

[Lidsavr]

I am writing code for Excel 2003

I am stuck on converting a formula to the VBA equivalent. The formula is:

=SUMIF(AB4:AD4,">10")/MAX(1,COUNTIF(AB4:AD4,">10"))

This formula works great in a cell on the spreadsheet!


In attempting to write the code, I've tried:

ActiveCell.Formula = "=SUMIF(AB4:AD4,">10")/MAX(1,COUNTIF(AB4:AD4,">10"))

However, I get a compile error: 'Expected End of Statement' at the first quotation mark after the range.

I've also tried writing the VBA equivalent, but get stuck on the correct syntax.

Can anyone help me convert this formula to code?

Thank you,

Charles

 

[VoG]

Try

x = Evaluate("SUMIF(AB4:AD4,"">10"")/MAX(1,COUNTIF(AB4:AD4,"">10""))")

 

[Lidsavr]

VoG,
That is it! Thank you very much.

Charles

 

[Scott Huish]

ActiveCell.Formula = "=SUMIF(AB4:AD4,"">10"")/MAX(1,COUNTIF(AB4:AD4,"">10""))"

Note that unless you have a specific reason for using ActiveCell, you don't need to select ranges to work with them in VBA, you can work with ranges directly:

Range("A3").Formula = "=SUMIF(AB4:AD4,"">10"")/MAX(1,COUNTIF(AB4:AD4,"">10""))"