Hi

I have written some code, which works, but I am sure should be much shorter
and simpler.

What I need to do is from column B in row 2 is enter the months from the
begining of the current year to date, in the format JANUARY [2009], c2 would
be FEBRUARY [2009] etc. I am currently calculating the month number ie april
= 4 and then have a series of IF's so that I get the correct number of
columns set up.

January is always added in B2 the rest use the following


If strMonthNum = 2 Then
range("C2").Formula = "February [" & strYear & "]"
End If

If strMonthNum = 3 Then
range("C2").Formula = "February [" & strYear & "]"
range("D2").Formula = "March [" & strYear & "]"
End If


If strMonthNum = 4 Then
range("C2").Formula = "February [" & strYear & "]"
range("D2").Formula = "March [" & strYear & "]"
range("E2").Formula = "April [" & strYear & "]"
End If



etc etc etc.


What is the best way to reduce the number of lines of code?

Thanks
Richard

Try this..

Sub FillMonths()
For intTemp = 1 To 12
Cells(2, 1 + intTemp) = MonthName(intTemp) & " [" & Year(Date) & "]"
Next
End Sub

If this post helps click Yes
---------------
Jacob Skaria


"Richard" wrote:

> Hi
>
> I have written some code, which works, but I am sure should be much shorter
> and simpler.
>
> What I need to do is from column B in row 2 is enter the months from the
> begining of the current year to date, in the format JANUARY [2009], c2 would
> be FEBRUARY [2009] etc. I am currently calculating the month number ie april
> = 4 and then have a series of IF's so that I get the correct number of
> columns set up.
>
> January is always added in B2 the rest use the following
>
>
> If strMonthNum = 2 Then
> range("C2").Formula = "February [" & strYear & "]"
> End If
>
> If strMonthNum = 3 Then
> range("C2").Formula = "February [" & strYear & "]"
> range("D2").Formula = "March [" & strYear & "]"
> End If
>
>
> If strMonthNum = 4 Then
> range("C2").Formula = "February [" & strYear & "]"
> range("D2").Formula = "March [" & strYear & "]"
> range("E2").Formula = "April [" & strYear & "]"
> End If
>
>
>
> etc etc etc.
>
>
> What is the best way to reduce the number of lines of code?
>
> Thanks
> Richard
>

Jacob

Brilliant thanks.

"Jacob Skaria" wrote:

> Try this..
>
> Sub FillMonths()
> For intTemp = 1 To 12
> Cells(2, 1 + intTemp) = MonthName(intTemp) & " [" & Year(Date) & "]"
> Next
> End Sub
>
> If this post helps click Yes
> ---------------
> Jacob Skaria
>
>
> "Richard" wrote:
>
> > Hi
> >
> > I have written some code, which works, but I am sure should be much shorter
> > and simpler.
> >
> > What I need to do is from column B in row 2 is enter the months from the
> > begining of the current year to date, in the format JANUARY [2009], c2 would
> > be FEBRUARY [2009] etc. I am currently calculating the month number ie april
> > = 4 and then have a series of IF's so that I get the correct number of
> > columns set up.
> >
> > January is always added in B2 the rest use the following
> >
> >
> > If strMonthNum = 2 Then
> > range("C2").Formula = "February [" & strYear & "]"
> > End If
> >
> > If strMonthNum = 3 Then
> > range("C2").Formula = "February [" & strYear & "]"
> > range("D2").Formula = "March [" & strYear & "]"
> > End If
> >
> >
> > If strMonthNum = 4 Then
> > range("C2").Formula = "February [" & strYear & "]"
> > range("D2").Formula = "March [" & strYear & "]"
> > range("E2").Formula = "April [" & strYear & "]"
> > End If
> >
> >
> >
> > etc etc etc.
> >
> >
> > What is the best way to reduce the number of lines of code?
> >
> > Thanks
> > Richard
> >