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

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
 
M

Matthew Herbert

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
 
E

EricG

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

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

HTH,

Eric
 
P

Patrick Molloy

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...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to copy rows that contain a cell with a certain number 3
Macro that deletes and pastes filterd rows and then some 2
Checkbox linked to calcs 1
Checkbox linked to calcs 0
how to use Solver in Excel VBA 1
Data samples 3
Macro 3
Link Cells & Paste Special Macro 7

Top