"=TEXT(RC[-1],"MMM")"

  • Thread starter Thread starter J.W. Aldridge
  • Start date Start date
J

J.W. Aldridge

I have a date in i2, that I need the month returned in j2.
I converted the following formula to be placed in j2, but I am getting
a compile syntax error on the MMM.


Range("J2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],"MMM")"

Here's the entire code if needed:

Sub Setup()

Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>0,(ROUNDDOWN(RC[-1]*24,0)/24)-""1:00""+(RC[-1]<TIME
(1,0,0)),"""")"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R1C[2]:R4C[3],2)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=TIME(23,0,0), RC[-4]-1, RC
[-4])"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],"MMM")"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,RC[-2]+(WEEKDAY(RC[-2])>7)
*7-WEEKDAY(RC[-2])+7,"")"

Range("G2:K2").Select
Selection.AutoFill Destination:=Range("G2:I65536")
Range("G2:K65536").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
 
J.W. Aldridge,

You are embedding text in the formula and the compiler requires extra double
quotes when you insert text in this context. Your ROUNDDOWN formula has it
right, so reference that for details.

NOT "=TEXT(RC[-1],"MMM")"

BUT "=TEXT(RC[-1],""MMM"")"

Best,

Matthew Herbert
 
You need an extra " at the start and end of "MMM":

ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],""MMM"")"

HTH,

Eric
 
due to my slack respones, here's an upgrade of your code :)

Sub Setup()

Range("G2").FormulaR1C1 = _

"=IF(RC[-1]>0,(ROUNDDOWN(RC[-1]*24,0)/24)-""1:00""+(RC[-1]<TIME(1,0,0)),"""")"
Range("H2").FormulaR1C1 = "=VLOOKUP(RC[-2],R1C[2]:R4C[3],2)"
Range("I2").FormulaR1C1 = "=IF(RC[-2]=TIME(23,0,0), RC[-4]-1, RC[-4])"
Range("J2").FormulaR1C1 = "=TEXT(RC[-1],""MMM"")"
Range("K2").FormulaR1C1 =
"=IF(RC[-2]>0,RC[-2]+(WEEKDAY(RC[-2])>7)*7-WEEKDAY(RC[-2])+7,"""")"

Range("G2:K65536").FillDown
Range("G2:K65536").Value = Range("G2:K65536").Value


End Sub


Patrick Molloy said:
"=TEXT(RC[-1],""MMM"")"

J.W. Aldridge said:
I have a date in i2, that I need the month returned in j2.
I converted the following formula to be placed in j2, but I am getting
a compile syntax error on the MMM.


Range("J2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],"MMM")"

Here's the entire code if needed:

Sub Setup()

Range("G2").Select
ActiveCell.FormulaR1C1 = _
"=IF(RC[-1]>0,(ROUNDDOWN(RC[-1]*24,0)/24)-""1:00""+(RC[-1]<TIME
(1,0,0)),"""")"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-2],R1C[2]:R4C[3],2)"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]=TIME(23,0,0), RC[-4]-1, RC
[-4])"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=TEXT(RC[-1],"MMM")"
Range("K2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-2]>0,RC[-2]+(WEEKDAY(RC[-2])>7)
*7-WEEKDAY(RC[-2])+7,"")"

Range("G2:K2").Select
Selection.AutoFill Destination:=Range("G2:I65536")
Range("G2:K65536").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
End Sub
Click to expand...
Click to expand...
 
Back
Top