ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   "=TEXT(RC[-1],"MMM")" (https://www.excelbanter.com/excel-programming/434138-%3Dtext-rc%5B-1%5D-mmm.html)

J.W. Aldridge
"=TEXT(RC[-1],"MMM")"
 
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

Matthew Herbert[_3_]
"=TEXT(RC[-1],"MMM")"
 
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

"J.W. Aldridge" wrote:

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

EricG
"=TEXT(RC[-1],"MMM")"
 
You need an extra " at the start and end of "MMM":

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

HTH,

Eric

"J.W. Aldridge" wrote:

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

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

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

"J.W. Aldridge" wrote:

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

Patrick Molloy[_2_]
"=TEXT(RC[-1],"MMM")"
 
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" wrote:


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

"J.W. Aldridge" wrote:

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


All times are GMT +1. The time now is 01:51 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com