Macro to Create Dates from Date-1 to Date-N (OFFICE97 SR2)

Topic

New Reply

Edited by epic60sman on 14-Nov-01 11:25.

Hi Everyone:
I need help with the attached worksheet today, if possible. The data will be pasted into the sheet into cell B2. The current data table, shaded yellow, shows data for 7 tasks (A through G). I have to allow for the possiblility of up to 100 or more tasks. Total days, shaded blue, is computed using the Networkdays function (I will be entering the holidays in later).
All I need is a Macro to create the dates in Column J starting with the minimum

Reply | Quote

Replies

If you put the formula below in J5 and copy it down as far as the max number of lines, will it do what you need?

=IF(ISERROR(J4+1),"",IF(J4+1>MAX($D$3:$D$100),"",J4+1))

Reply | Quote

Not sure if I’ve understood correctly, but this would be one way to create a date list in a macro:

Sub FillDates()
Dim dtMin As Date
Dim dtMax As Date

dtMin = Application.WorksheetFunction.Min(Range(Range(“C3”), Range(“C65536”).End(xlUp)))
dtMax = Application.WorksheetFunction.Max(Range(Range(“D3”), Range(“D65536”).End(xlUp)))
With Range(“J4”)
.Value = dtMin
.DataSeries Rowcol:=xlColumns, Type:=xlChronological, Date:= _
xlDay, Step:=1, Stop:=dtMax, Trend:=False
End With
End Sub

HTH

Graeme

Reply | Quote

Thank you both for the quick response.
LEGARE:
Your equation works fine but I, while you are on a roll, I need advise on 3 other issues:
1) creating the column headers (one for each task) out of the task names (there could be 100 or more in column ,
2) copying thecell L2 (which now has the equation, = K2+1) across to the last task-headed column and entering the “total” header in row 3 of the next column; and
3) I entered the following equation, into R4, for totalling the rows, =SUM(OFFSET($K4,0,0,1,INDIRECT(ADDRESS(2,COLUMN()-1))-$K$2+1))
I need to have this equation entered into the first cell under the “total” header, so that I can invoke code to fill it down all the rows.
I have attached the file again to assist in seeing what I am talking about.
Thanks so much for your help.
Stephen

Reply | Quote

Is this something like what you wanted?

Dim I As Long, lSumCol As Long
    I = 0
    With Worksheets("Chart_Resources")
        While .Range("B3").Offset(I, 0).Value  ""
            .Range("K2").Offset(0, I).Value = I + 3
            .Range("K3").Offset(0, I).Value = .Range("B3").Offset(I, 0).Value
            I = I + 1
        Wend
    End With
    lSumCol = I
    I = 0
    While Worksheets("Chart_Resources").Range("J4").Offset(I, 0).Value  0
        Worksheets("Chart_Resources").Range("K4").Offset(I, isumcol).Formula = _
          "=Sum(K" & I + 4 & ":" & Range("K4").Offset(I, lSumCol - 1).Address(False, False) & ")"
        I = I + 1
    Wend

Reply | Quote

Legare:
I dunno. I will have to try it. I edited my post and our mails crossed. I really did not change anything except to tell you that your equation worked fine.
I will be back to you shortly.
Stephen

Reply | Quote

Legare:
Here are the results of testing your code:
1. It successfully entered the task names in the correct positions and the numbbers, starting with 3, in the row above the Task Names;
2. It also entered the correct sum equation,”=SUM(KX:QX)” , where X goes from 4 to 110, the number of days in the project. BUT it entered it into the first column (Column K), destroying the imbedded equations in that col. If this is corrected, I am sure I will have something I can use. But could you also put the “Total” header in the totals column?

I looked at your code again and the problem I could see was that I thought that Offset(I, sumcol-1) term in
“=Sum(K” & I + 4 & “:” & Range(“K4”).Offset(I, lSumCol – 1).Address(False, False) & “)”
should be .Offset(I, sumcol) because IsumCol starts off with a value of 7 (for this data set) and we want to sum 7 columns and also that the “.Offset(I, isumcol).Formula =” term should be “.Offset(I, isumcol+1).Formula =” because we want the formula to be in column R. I tried it this way but I must have messed something up.
I will try again.
If you can respondin the next hour, I will still be able to make my deadline.
Thanks again,
Your Faithful Student

Reply | Quote

Sorry about the bug. This should fix it.

Dim I As Long, lSumCol As Long
    I = 0
    With Worksheets("Chart_Resources")
        While .Range("B3").Offset(I, 0).Value  ""
            .Range("K2").Offset(0, I).Value = I + 3
            .Range("K3").Offset(0, I).Value = .Range("B3").Offset(I, 0).Value
            I = I + 1
        Wend
        .Range("K3").Offset(0, I).Value = "Total"
    End With
    lSumCol = I
    I = 0
    While Worksheets("Chart_Resources").Range("J4").Offset(I, 0).Value  0
        Worksheets("Chart_Resources").Range("K4").Offset(I, lSumCol).Formula = _
          "=Sum(K" & I + 4 & ":" & Range("K4").Offset(I, lSumCol - 1).Address(False, False) & ")"
        I = I + 1
    Wend

Reply | Quote

Legare:
This works great. Thank you. My last problem:
I thought that I knew how to FillRight but the following code (meant to copy the formula in K4 over to the last Task Column):
LastCol = Worksheets(“Chart_Resources”).Range(“D1”).Value
Worksheets(“Chart_Resources”).Range(“K4”).Activate
FillRtRange = Worksheets(“Chart_Resources”).Range(“K4”).Offset(rowOffset:=0, columnOffset:=LastCol)
Range(“FillRtRange”).FillRight

I Added Dim FillRtRange as Range and Dim LastCol as Long
and I also added lastCol = WorkSheets(“Chart_Resources”).Range(“D1”).Value and had a value of 7 in that cell.

It gives me an error.
HELP

Reply | Quote

I think that what you want is something like this:

Dim lLastCol As Long
    lLastCol = Worksheets("Chart_Resources").Range("IV4").End(xlToLeft).Column - 2
    Worksheets("Chart_Resources").Range("K4", Range("A4").Offset(0, lLastCol)).FillRight

Of course that would have to be adjusted to work on different rows.

Reply | Quote

That’s it!
Thanks

Reply | Quote