Formula Question in Macro

[helpme20]

my end goal is thatevery month you run the macro and get : "**this month** pricing" and"**next month** pricing"



I get an #Name error when I run this.

=MONTHNAME()&" Price"


Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
Range("U1").Select
ActiveCell.FormulaR1C1 = "=MONTHNAME()+30&"" Price"""
Range("Q1").Select
ActiveCell.FormulaR1C1 = "=MONTHNAME()&"" Price"""
Range("U1").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-1]=""Call for Pricing"",RC[-1],RC[-2])"

 

[Scott Huish]

MONTHNAME is a VBA function, not a spreadsheet function. Also you haven't given it any parameters.

An equivalent formula that can be used in the spreadsheet, where A1 (in this sample) holds your month number.
=TEXT(A1*29,"mmmm")

 

[helpme20]

the excerpt I sent was from a Macro.

 

[helpme20]

MONTHNAME is a VBA function, not a spreadsheet function. Also you haven't given it any parameters.

An equivalent formula that can be used in the spreadsheet, where A1 (in this sample) holds your month number.
=TEXT(A1*29,"mmmm")

Any other suggestions?

 

[Scott Huish]

I know that the excerpt was from a macro. My point is that you can't put a formula into a spreadsheet that contains non-spreadsheet functions (unless you write a UDF for it). That's why I offered an alternative formula for you to use in your macro. Adapt that formula as necessary.

 

[Norie]

Perhaps.

Range("U1").Value = MonthName(Month(Now)+1) & " Price"
Range("Q1").Value = MonthName(Month(Now)) & " Price"
Range("U1").FormulaR1C1 = "=IF(RC[-1]=""Call for Pricing"",RC[-1],RC[-2])"