Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    <P ID="edit" class=small>Edited by epic60sman on 14-Nov-01 11:25.</P>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
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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?

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

    Legare Coleman

  3. #3
    New Lounger
    Join Date
    Sep 2001
    Location
    Paignton, Devon, England
    Posts
    21
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Is this something like what you wanted?

    <pre>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
    </pre>

    Legare Coleman

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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 [img]/forums/images/smilies/cool.gif[/img],
    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
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  7. #7
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Sorry about the bug. This should fix it.

    <pre>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
    </pre>

    Legare Coleman

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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(r owOffset:=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
    <img src=/S/help.gif border=0 alt=help width=23 height=15>

  10. #10
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    I think that what you want is something like this:

    <pre>Dim lLastCol As Long
    lLastCol = Worksheets("Chart_Resources").Range("IV4").End(xlT oLeft).Column - 2
    Worksheets("Chart_Resources").Range("K4", Range("A4").Offset(0, lLastCol)).FillRight
    </pre>


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

  11. #11
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Baltimore, MD, Maryland, USA
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    That's it!
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •