VBA to add month and year to text

[ROBINSON_US]

I have this line of code, which adds a title to a report I've created:

ActiveCell.FormulaR1C1 = "ABC COMPANY INC. - SALES COMMISSION REPORT FOR JULY 2017"

Works great, but as each month goes by, I'd like to not have to go into the code and change the name of the month. Plus, when I do the reports, I'm in the next month (I'm doing July's reports in August, etc.) So, is there something that could look like:

ActiveCell.FormulaR1C1 = "ABC COMPANY INC. - SALES COMMISSION REPORT FOR " (plus code to add previous month's name)?

Thanks

 

[FormR]

Hi, you could try:

ActiveCell.Value = "ABC COMPANY INC. - SALES COMMISSION REPORT FOR " & UCase(Format(DateAdd("m", -1, Date), "MMM YYYY"))

 

[gallen]

Not sure why you are using .formulaR1C1 but the answer to your question is:

ActiveCell.FormulaR1C1 = "ABC COMPANY INC. - SALES COMMISSION REPORT FOR " & UCase(Format(DateAdd("m", -1, Now), "mmmm yyyy"))

 

[ROBINSON_US]

Thank you!!