Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code with eomonth problems
i have eomonth formulas in b5:b16. they are formatted in month name MMMM
this code runs until it gets to october, the 10th month, and throws a 1004 error, so oct, nov and dec do not update. anyone know what the difference would be? c1 contains the name of the workbook example formula the code creates: =Kelly.xls!Feb_Short i = 5 For i = 5 To 16 Cells(i, 3).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Short" Cells(i, 4).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Over" Next -- Gary |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code with eomonth problems
try changing
Format(Left(Cells(i, 2), 3) ,"MMM") to Left(Cells(i, 2).Text, 3) In both cases. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i have eomonth formulas in b5:b16. they are formatted in month name MMMM this code runs until it gets to october, the 10th month, and throws a 1004 error, so oct, nov and dec do not update. anyone know what the difference would be? c1 contains the name of the workbook example formula the code creates: =Kelly.xls!Feb_Short i = 5 For i = 5 To 16 Cells(i, 3).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Short" Cells(i, 4).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Over" Next -- Gary |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code with eomonth problems
seems to work, thanks tom.
do you know why format stopped at the 10th month? -- Gary "Tom Ogilvy" wrote in message ... try changing Format(Left(Cells(i, 2), 3) ,"MMM") to Left(Cells(i, 2).Text, 3) In both cases. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i have eomonth formulas in b5:b16. they are formatted in month name MMMM this code runs until it gets to october, the 10th month, and throws a 1004 error, so oct, nov and dec do not update. anyone know what the difference would be? c1 contains the name of the workbook example formula the code creates: =Kelly.xls!Feb_Short i = 5 For i = 5 To 16 Cells(i, 3).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Short" Cells(i, 4).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Over" Next -- Gary |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code with eomonth problems
Basically because you were lucky that it worked for months less than
October - the fact it worked was mostly coincidental. From the immediate window: activeCell.NumberFormat = "MMM" activecell.Value = DateValue("3/31/2006") ? left(activeCell,3) 3/3 ? format(left(activeCell,3),"MMM") Mar ActiveCell.Value = DateValue("10/31/2006") ? Left(activecell,3) 10/ ? format(left(activecell,3),"MMM") 10/ With 1 digit months, the 3 digits on the left that were returned (digit slash digit) could still be interpreted as a date with the correct month. When you got to 2 digit months, the 3 digits on the left no longer could be interpreted as a date. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... seems to work, thanks tom. do you know why format stopped at the 10th month? -- Gary "Tom Ogilvy" wrote in message ... try changing Format(Left(Cells(i, 2), 3) ,"MMM") to Left(Cells(i, 2).Text, 3) In both cases. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i have eomonth formulas in b5:b16. they are formatted in month name MMMM this code runs until it gets to october, the 10th month, and throws a 1004 error, so oct, nov and dec do not update. anyone know what the difference would be? c1 contains the name of the workbook example formula the code creates: =Kelly.xls!Feb_Short i = 5 For i = 5 To 16 Cells(i, 3).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Short" Cells(i, 4).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Over" Next -- Gary |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code with eomonth problems
thanks for the explanation
-- Gary "Tom Ogilvy" wrote in message ... Basically because you were lucky that it worked for months less than October - the fact it worked was mostly coincidental. From the immediate window: activeCell.NumberFormat = "MMM" activecell.Value = DateValue("3/31/2006") ? left(activeCell,3) 3/3 ? format(left(activeCell,3),"MMM") Mar ActiveCell.Value = DateValue("10/31/2006") ? Left(activecell,3) 10/ ? format(left(activecell,3),"MMM") 10/ With 1 digit months, the 3 digits on the left that were returned (digit slash digit) could still be interpreted as a date with the correct month. When you got to 2 digit months, the 3 digits on the left no longer could be interpreted as a date. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... seems to work, thanks tom. do you know why format stopped at the 10th month? -- Gary "Tom Ogilvy" wrote in message ... try changing Format(Left(Cells(i, 2), 3) ,"MMM") to Left(Cells(i, 2).Text, 3) In both cases. -- Regards, Tom Ogilvy "Gary Keramidas" <GKeramidasATmsn.com wrote in message ... i have eomonth formulas in b5:b16. they are formatted in month name MMMM this code runs until it gets to october, the 10th month, and throws a 1004 error, so oct, nov and dec do not update. anyone know what the difference would be? c1 contains the name of the workbook example formula the code creates: =Kelly.xls!Feb_Short i = 5 For i = 5 To 16 Cells(i, 3).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Short" Cells(i, 4).Formula = "=" & Range("c1") & ".xls!" & Format(Left(Cells(i, 2), 3), _ "MMM") & "_Over" Next -- Gary |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I'm Having Problems with EOMONTH | Excel Worksheet Functions | |||
I'm having problems with EOMONTH | Excel Worksheet Functions | |||
sorting code problems | Excel Programming | |||
Problems merging an excel file due to code or file problems? | Excel Programming | |||
VBA Code problems! | Excel Programming |