Calculate Month
 

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

Calculate Month
 

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

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

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!