Calculate Month

[Guest]

I have a table with column headers

8/1/2004 9/1/2004 10/1/2004 ... 3/1/2005

I have to find the column that is 5 months from today and insert a column.
If I run the macro on the 1st of the month or the 31st of the month I should
find the same column.

Thanks,
Denise

 

[Sean]

Dear Denise,

Maybe the following code would help. Choose one of the r.Cells....Insert
lines depending on whether you need the inserted column before or after
the target month

----
Sean
"Just press the off switch, and go to sleep!"

'''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''
Sub FindAndInsert()
Dim d As Long, r As Range, i As Long

' assuming the months are in the first row of our worksheet
Set r = ActiveSheet.Rows(1)

' first get the current date PLUS 5 months
d = Now()
d = DateSerial(Year(d), Month(d) + 5, 1)

' use the MATCH() function to find our valid month
' if no match if found then just quit
On Error GoTo Exit_proc
i = Application.WorksheetFunction.Match(d, r, 0)

' either insert a column BEFORE the one found
r.Cells(1, i).EntireColumn.Insert

' or insert a column AFTER the one found
'r.Cells(1, i + 1).EntireColumn.Insert

Exit_proc:
Exit Sub
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''''''''''''''''''''''''''''''''