Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Adding row to schedule, drag fromulas down (Excel 2000)

    Hi Loungers,

    I have a work sheet that I have made which is a schedule.
    I have build some macro funtionality into it.

    what my worksheet is suppose to do.
    I have a button, which adds a row above the selected cell.

    I am trying to acheive an easy way to add a stage in between other stages and reset the formulas in the white cells to be counted in the added row. (stages are rows in this worksheet.)

    I have a limit on where I can add stages betwwen rows 25 and 65.

    when the button is click it adds a row above the select cell, It then drags the formula from an above cell so it can be included in the caluclations (white columns.)

    The method I have does not fully update the formulas.
    I am not sure what kind of code I could used to update the formulas so they are consistant with the new row. for example if a stage is added the formuals are updateded but the row below the new row does not get updated and refers to the row two rows above.

    I also have some formulas to the far left that aid the calculations that need this updating

    anyone have a good idea that can update my formulas to aid the new row and calculate properly?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    Do you want to fill down only the formulas or also the values (in the green columns)?

  3. #3
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    just formulas

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    Try

    Sub InsertStage()
    Dim lngRow As Long
    lngRow = ActiveCell.Row
    If lngRow < 25 Or lngRow > 64 Then
    MsgBox "You must select a row within the proper stages!"
    Exit Sub
    End If
    ActiveCell.EntireRow.Insert
    FillColumn lngRow, 3, 8, 9, 12, 13
    End Sub

    Sub FillColumn(lngRow As Long, ParamArray varCol())
    Dim i As Integer
    For i = LBound(varCol) To UBound(varCol)
    Range(Cells(lngRow - 1, varCol(i)), Cells(lngRow + 1, varCol(i))).FillDown
    Next i
    End Sub

  5. #5
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    wow, out of my league,

    can you explain please how does the code know where to copy down to.
    I am wondering because it is able to handle adding a row dragging down to the proper ending, and then adding a another row later and draging down to the correct ending before the ending I had was hard coded row 64 i believe.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    In the line

    FillColumn lngRow, 3, 8, 9, 12, 13

    the 3, 8 etc. are the numbers of the columns with formulas (1 = A, 2 = B etc.), so the numbers correspond to columns C, H, I, L and M.

    The line that fills down is

    Range(Cells(lngRow - 1, varCol(i)), Cells(lngRow + 1, varCol(i))).FillDown

    This fills down in one column from the cell in the row above the active cell (lngRow - 1) to the row below the active cell (lngRow + 1), to ensure that the formulas remain consistent.

  7. #7
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    nice job,

    I am wondering if there is a way move the data down a row, instead of inserting a row, so that the rows dont change but the data moves down one row and above there is not a place to enter a new stage?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    Sorry, I don't understand. If you move data down a row, you automatically free up a row, don't you? So the effect would be the same as inserting a row. Obviously, you have something else in mind - please explain.

  9. #9
    2 Star Lounger
    Join Date
    Oct 2005
    Location
    Calgary, Alberta
    Posts
    205
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    I am making another worksheet which reports the data basically by referencing the the table of stage from the previous worksheet.

    if I add a row (stage) to the INPUT 2 worksheet, then my reporting worksheet does not show the added stage nor compensate for the added stage.

    my thinking was that instead of inserting a row, move the data from the selected row and now have a blank stage above, the report sheet would update correctly for the change

    there would be boundaries, rows 25 : 61any rows between can be moved done burt cant exceed the boundary. Does this make sense?

    or is there an easier way to have my INPUT stage sheet update my report sheet?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Adding row to schedule, drag fromulas down (Excel 2000)

    You could add code to the InsertStage macro to
    a) insert a row in the appropriate place in the Report sheet.
    [img]/forums/images/smilies/cool.gif[/img] fill down the formulas to make them consistent again.
    The code would look very similar to the code you already have, but you have to take care to refer to the Report sheet

Posting Permissions

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