Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > "=TEXT(RC[-1],"MMM")"
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 425
Default "=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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 149
Default "=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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 220
Default "=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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default "=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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,298
Default "=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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell Steve Kay Excel Discussion (Misc queries) 2 August 8th 08 01:54 AM
change "true" and "false" to "availble" and "out of stock" inthestands Excel Worksheet Functions 2 July 19th 07 07:05 PM
HELP on "left","right","find","len","substitute" functions serene83 Excel Discussion (Misc queries) 5 June 27th 06 02:23 AM
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next BCB New Users to Excel 7 May 13th 06 10:02 PM
Insert "-" in text "1234567890" to have a output like this"123-456-7890" Alwyn Excel Discussion (Misc queries) 3 October 25th 05 11:36 PM


All times are GMT +1. The time now is 08:39 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement