Column Heading Loop
 

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

Column Heading Loop
 

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

Column Heading Loop
 

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