Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Autofill (Excel 2000)

    It's me today, but my mind is not clicking...
    If I want to take for example 08/01/04 and autofill only the 1st and the 15th of every month how is this accomplished. I know there's got to be a way to tell it, to pick the 1st and the 15th of every month and fill the cells in my highlighted area no matter how far I wish to autofill. I have thought about using the Custom list and just typing what I need 26 times, but there must be another way.
    Thanks...
    NMP
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Autofill (Excel 2000)

    Autofill works on trends and there is no trend in the values listed. Here a routine. Select the range to fill, run the routine and you will be prompted for a start date. It will then fill in the selection (or teh first column if more than 1 column is selected)

    Steve

    <pre>Option Explicit
    Sub FillFirstFifteenth()
    Dim rng As Range
    Dim dDate As Date
    Dim iMonth As Integer
    Dim iYear As Integer
    Dim lRow As Long
    Set rng = Selection
    dDate = DateValue(InputBox("What is the start date"))

    iMonth = Month(dDate)
    iYear = Year(dDate)
    With rng
    For lRow = 1 To .Rows.Count Step 2
    .Cells(lRow, 1) = DateSerial(iYear, iMonth, 1)
    .Cells(lRow + 1, 1) = DateSerial(iYear, iMonth, 15)
    iMonth = iMonth + 1
    Next
    End With
    End Sub</pre>


  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Jacksonville,NC, USA
    Posts
    705
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autofill (Excel 2000)

    This works very well.
    Thank you very much.
    NMP [img]/forums/images/smilies/smile.gif[/img]
    NMP <img src=/S/cool.gif border=0 alt=cool width=15 height=15>

    If you can't convince them, confuse them. - Harry Truman <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29>

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

    Re: Autofill (Excel 2000)

    Assuming that you want 8/1/04 in A1, and then the 15th in A2, and so on, then first enter 8/1/04 in A1. Then enter this formula in A2:

    <pre>=DATE(YEAR(A1),MONTH(A1),15)
    </pre>


    Then enter this formula in A3:

    <pre>=DATE(YEAR(A2),MONTH(A2)+1,1)
    </pre>


    Now, select cells A2 and A3 and copy down as far as you want to go.
    Legare Coleman

Posting Permissions

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