Results 1 to 10 of 10

20061031, 16:55 #1
 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?

20061031, 17:18 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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)?

20061031, 17:20 #3
 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

20061031, 17:26 #4
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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

20061031, 17:41 #5
 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.

20061031, 17:46 #6
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20061031, 21:35 #7
 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?

20061031, 21:42 #8
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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.

20061031, 21:59 #9
 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?

20061031, 22:08 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 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