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

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

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

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

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