Using TEXT Formula Function in VBA

[stratman]

Good day all,

Trying to use this code to insert a formula in a cell in VBA and am running into issues:

Cells(35, 1).Formula = "Expenditures YTD Since " & TEXT(I35, "mmmm yyyy")

I believe the TEXT formula function and the Text command in VBA are conflicting. Suggestions/solutions?


Thanks for your review and consideration.

jski

 

[Marc L]

Hi !​

​

As there is no VBA 'Text' function …​

​

It seems you are wrong with the double quotes but try first the formula manually in a cell and once it works​

post here this working cell formula with an explanation of what you expect under VBA …​

​

 

[stratman]

Thanks Marc,

The formula is correct as presented above and works in the cell...just can't get it to work as coded in VBA. Cell I35 is a calculated date based on this formula:

=IF(MONTH(A1)<J35,DATE(YEAR(A1)-1,6,1),DATE(YEAR(A1),6,1))

Cell A1 is =TODAY()
Cell J35 is '6'

The formula above finds the fiscal year of today's date and presents it as June 2021.

What I'm trying to do is combine the defined text with the calculated date. The result would be:

Expenditures YTD Since June 2021


Is that helpful?

 

[Marc L]

As it is your 'expenditures' formula can't work ! So try first manually then once the formula works post it here exactly as it is in the cell …​

If you do not understand so attach at least your workbook with the cell A35 working formula.​

​

As a reminder under VBA you must double each double quote within a cell formula …​

​

 

[p45cal]

Either:
Cells(35, 1).FormulaR1C1 = "=""Expenditures YTD Since "" & TEXT(RC[8], ""mmmm yyyy"")"
or:
Cells(35, 1).Formula = "=""Expenditures YTD Since "" & TEXT(I35, ""mmmm yyyy"")"

 

[stratman]

Outstanding gentlemen. p45cal---both of those options worked. Thanks.

Marc L--good eye on the double quotes. I was missing a few and also misplaced one. Merci beaucoup!

 

[Marc L]

Yes like the missing equal sign as without it can't be a formula, the reason why I asked for the cell formula 'as it is' …​

​