Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want column heading non-scrolling and rows of each column scrol | New Users to Excel | |||
How do I put a heading even with a column? | New Users to Excel | |||
In a table produce an value by column heading and row heading | Excel Worksheet Functions | |||
Find Column heading and then Delete entire column | Excel Programming | |||
Need Macro to Find Column Heading -- if none, then insert new column | Excel Programming |