VBA to insert Min/Max in cell

[Calvin_Hobbes]

Hi,
I need to insert formulas using VBA and all work but the Min and Max formulas.

This formula when entered directly into a worksheet cell with the formula bar works as it should: =IF(MAX(REPORT!G2:G21)=0, ", MAX(REPORT!G2:G21))

But when entered using VBA it is not recognized as a formula and renders as plain text:
Worksheets("Dashboard").Range("TEST").Formula = " =IF(MAX(REPORT!G2:G21)=0, "", MAX(REPORT!G2:G21))"

The Min/Max formula is wrapped in the IF statement so that if there is no date present in the indicated range the cell will be empty rather than showing 1900-01-00.

I need to be able to insert the formula(s) using VBA as my worksheet is inserted into another workbook and then posted to Box.
If I insert the sheet with formulas already in place the path to my local workbook is added to the formula(s) and I have to go to each one and delete the reference.

Any help would be appreciated.

 

[Joe4]

Welcome to the Board!

A few things.
Get rid of the space before the equals sign.
You need to double-up on your double-quotes, as they are used as Text Indicators, though you also want literal ones.

So try this:

Worksheets("Dashboard").Range("TEST").Formula = "=IF(MAX(REPORT!G2:G21)=0,"""",MAX(REPORT!G2:G21))"

 

[Calvin_Hobbes]

Perfect! Thanks so much for your help! It's the simplest things that get you sometimes

 

[Joe4]

You are welcome.
Glad I was able to help!