Advice: Help with a macro to insert and delete rows

[TButhe]

I am sure this is far easier than I am making it. I hope someone out there can just tell me why this isn't working. I want to create two macros for this spreadsheet. They should be fairly easy but I can't seem to get them to work. This is sort of a checkbook balance that our facilties/centers will use each month. I want one macro to insert a new row and add the formulas to that row.The other needs to delete a row and adjust the formulas. My problem is that the formulas that I have are dependant on the row above it. (For example, in G17 the formula should be =SUM(G16-E17+E18) I need to have the formulas below the insertion or deletion updated. I have tried just recording the macro but the formulas change when they are copied. I tried using the relative references but that doesn't work either. I tried the code that was put out in August(I think it was Aug.) but that didn't help either. What am I missing?? I apologize if I missed something that would answer my question. I am such a NEWBIE!!

I'm using Windows 2000 and Excel XP.
Thanks in advance.
Here are my two attempts.

Macro recorded 11/30/2004 
ActiveSheet.Unprotect
    Selection.End(xlToLeft).Select
    Selection.EntireRow.Insert
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C+1)"
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C-RC[-1]+RC[-1])"
    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Sub Delete()
' Delete Macro
' Macro recorded 11/30/2004 
    ActiveSheet.Unprotect
    Selection.End(xlToLeft).Select
    ActiveCell.Range("A1:F1").Select
    Selection.Delete Shift:=xlUp
    Selection.End(xlToLeft).Select
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.copy
    ActiveCell.Offset(1, 0).Range("A1").Select
    ActiveSheet.Paste
    ActiveCell.Offset(0, 6).Range("A1").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-1]C-RC[-2]+RC[-1])"
    Selection.End(xlDown).Select
    Selection.ClearContents
    Selection.ClearFormats
    ActiveCell.Offset(-1, 0).Range("A1").Select
    Selection.End(xlUp).Select
    ActiveCell.Offset(1, -5).Range("A1").Select
    ActiveSheet.protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

[johnske]

Hi TButhe,

Welcome to VBAX. I haven't looked too hard at what you're attempting to do - but - I try to avoid R1C1 referencing wherever possible (as I get lost with it) and use the native A1 style (where columns are given as letters and rows are given as numbers).

Just a few points: If a dollar sign precedes the letter and/or number, such as $A$1, the column and/or row reference is absolute. Without any dollar signs (i.e. A1), the reference is relative and automatically adjusts for the new column and row when you copy it to another position on the spreadsheet (where-as absolute references don't).

You can have variations on this such as
i) $A1 - this adjusts for rows, but always references the same column(A), or
ii) A$1 - this adjusts for columns, but always references the same row(1).

Now, instead of something like this: ActiveCell.FormulaR1C1 = "=SUM(R[-1]C-RC[-1]+RC[-1])"
you can use: ActiveCell.Formula = "=SUM(G16-E17+E18)" in your macro, this is relative, if you want to make it an absolute reference you would use
ActiveCell.Formula = "=SUM($G$16-$E$17+$E$18)" instead...

Hope this helps...

[mdmackillop]

Have you tried giving the cells range names, then the formulas would be something like =Sum(Tot1-Tot2+Tot3), or are you deleting the lines wich contain these cells?
MD

[TButhe]

I used the relative references and what happens is the current row is fine but all the rows after are changed to something like =SUM(G17-E17+F17) instead of =SUM(G16-E17+F17). (It doesn't copy this formula exactly, each row has its own row number in the formula.) Maybe I just need to find a way to copy the formula into the rest of the remaining cells in that column but I have no way of knowing how many rows will be in each spreadsheet - it could vary a great deal center by center. Thanks again for the assistance. I really appreaciate it.

[johnske]

I used the relative references and what happens is the current row is fine but all the rows after are changed to something like =SUM(G17-E17+F17) instead of =SUM(G16-E17+F17). (It doesn't copy this formula exactly, each row has its own row number in the formula.) Maybe I just need to find a way to copy the formula into the rest of the remaining cells in that column but I have no way of knowing how many rows will be in each spreadsheet - it could vary a great deal center by center. Thanks again for the assistance. I really appreaciate it.

Hi,

It seems you're trying to insert an exact duplicate of the formula on a different row, in that case use absolute references e.g. =SUM($G$16-$E$17+$F$17) instead...

HTH

[mdmackillop]

Hi TButhe,
Any chance you can zip and post the file here? A live example usually makes things easier to understand.
MD

[TButhe]

I attached the file. It is pretty simple. - No laughing at my feeble attempts at coding. Thanks for the suggestions. I have to say that I am really enjoying this forum. Keep up the great work everyone!

[mdmackillop]

We all start somewhere.
Anyway, yopur problem is clearer now. All the cells below your insertion require the data to be changed, also the index column (I presume)

Try the following code instead, it works by inserting a row, then using a FillDown procedure to copy the code from the cell above to the bottom of the column, thus correcting all the formulae. It does the same on the offset index column. The code is set to be run from a cell in Column G, although this is easily adjusted if required.

Sub DoFill()
    Dim Rg As Range, SRg As Range, Lrg As Range
ActiveCell.Rows("1:1").EntireRow.Insert Shift:=xlDown
'Adjust column G 
    Set Rg = ActiveCell.Offset(-1, 0)
    Set SRg = Range(ActiveCell, ActiveCell.End(xlDown))
    Set Lrg = Union(Rg, SRg)
    Rg.Select
    Selection.AutoFill Destination:=Lrg
'Adjust Column A     
    Set Rg = ActiveCell.Offset(0, -6)
    Set SRg = Range(ActiveCell.Offset(0, -6), ActiveCell.Offset(0, -6).End(xlDown))
    Set Lrg = Union(Rg, SRg)
    Rg.Select
    Selection.AutoFill Destination:=Lrg
Set Rg = Nothing
    ActiveCell.Select
End Sub

[mdmackillop]

I think you'll find if you change the Insert line to

ActiveCell.Rows("1:1").EntireRow.Delete

that the same coding will work for that as well (we all need a little bit of luck!)
MD

[TButhe]

Thanks for your help! I learned a lot, although we ended up revamping the form and this won't be necessary anymore.

all that hard work for nothing. Thanks again.