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

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
news:[email protected]...
> 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
>
>
>

seems to work, thanks tom.

do you know why format stopped at the 10th month?

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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
> news:[email protected]...
>> 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
>>
>>
>>
>
>

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
news:%[email protected]...
> seems to work, thanks tom.
>
> do you know why format stopped at the 10th month?
>
> --
>
>
> Gary
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > 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
> > news:[email protected]...
> >> 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
> >>
> >>
> >>
> >
> >
>
>

thanks for the explanation

--


Gary


"Tom Ogilvy" <[email protected]> wrote in message
news:umO%[email protected]...
> 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
> news:%[email protected]...
>> seems to work, thanks tom.
>>
>> do you know why format stopped at the 10th month?
>>
>> --
>>
>>
>> Gary
>>
>>
>> "Tom Ogilvy" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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
>> > news:[email protected]...
>> >> 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
>> >>
>> >>
>> >>
>> >
>> >
>>
>>
>
>