Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula to calculate month-month | Excel Discussion (Misc queries) | |||
calculate month | Excel Worksheet Functions | |||
Calculate days in a month | Excel Worksheet Functions | |||
calculate Age Year Month & day | Excel Discussion (Misc queries) | |||
Calculate the first day of the month for the current month? | Excel Discussion (Misc queries) |