Excel 2000: AutoSum or Insert Row in Protected Sheet using VB macros

You could try this too:

Dim myrng As Range

Set myrng = Cells(Rows.Count, "F").End(xlUp).Offset(1, 0)

myrng.FormulaR1C1 = "=sum(r7c:r[-1]c)"

'or

myrng.Formula = "=sum($f$7:offset(" & myrng.Address(False, False) _

& ",-1,0,1,1))"

the second formula looks funnier, but reacts to insertion of rows nicer. It

essential says to go from F7 to one above the cell that's getting the formula.

If you've ever used a formula like: =sum(F7:f22) in cell F23, then inserted a

new row 23, your formula doesn't adjust to include that row atomatically to

something like: =sum(f7:f23)

but if you used this formula:

=sum(f7:offset(f22,-1,0,1,1)) <--f7 to one cell above F22

and do the same insert row, then formula adjusts to

=SUM(F7:OFFSET(F23,-1,0,1,1)) <--f7 to one cell above f23

which can be very useful.

Quote:

> As is always the way, after posting this I returned to the problem and found

> a simple solution, for anybody having the same problem here is the code

> BEGIN CODE

> Dim endofvaluesrow As String

> Dim StringValue1 As String

> Dim StringValue2 As String

> Dim StringValue3 As String

> Dim StringValue4 As String

> Range("F1").Select

> Selection.End(xlDown).Select

> Selection.End(xlDown).Select

> endofvaluesrow = Format(ActiveCell.Row)

> ActiveCell.Offset(2, 0).Range("A1").Select

> StringValue2 = "F" + endofvaluesrow

> StringValue1 = "=SUM(F7:"

> StringValue3 = ")"

> StringValue4 = StringValue1 + StringValue2 + StringValue3

> ActiveCell.Formula = StringValue4

> END CODE

> Not very elegant, but it works even when the sheet is protected, though I

> still wish I could just access the AutoSum..

> Remember I know that the column is always F and the first cell is always F7

> so I could just plonk these in.

> Phil

> > I have an invoice sheet in Excel 2000 that uses a Lookup from an another

> > sheet that contains values, I have locked only the cells containing the

> > formulas so that the users can't change them, then protected the invoice

> > sheet.

> > My problem is that I don't know how many items are going to be included on

> > the invoice sheet so I can't put a total at the bottom.

> > I have been trying to approach this in two different ways using Visual

> Basic

> > macros,

> > Firstly by moving to a fixed point at the top of the sheet above the

> costs,

> > then using "Ctrl+Down" to reach the last row with data, then a simple Down

> > followed by an InsertRow then a selection of cells and a Ctrl+D to

> duplicate

> > the formulas.

> > This means that I can place a SUM formula two rows down from the last data

> > row and this will automatically update when I insert the rows. This works

> > great umtil I protect the sheet at which point InsertRow no longer works.

> > The second method involved trying to create my own AutoSum function, again

> > using a macro to Ctrl+Down to the last data row and then moving one row

> down

> > and inserting a formula="=sum(value1, value2)". This works great when

> using

> > either absolute or non-relative terms, but not when you try to combine

> them.

> > The first value, value1, is always "F7", however the seond value is always

> > [R-1C0] from the active cell and both FORMULA and FORMUALR1C1 doesn't

> allow

> > mixed terms. I've seen the CONVERTFORMULA method but again that only seems

> > to deal with converting one whole set of terms into another.

> > Even if I can covert [R-1C0] into an absolute term I still can't see a way

> > of entering it into the cell as neither the FORMULA or the SUM method seem

> > to allow variables of the FORMULA="SUM(F7:value2)" type.

> > What is especially annoying, is that in Word I could simply use an

> > "=SUM(ABOVE)" and all my problems would be solved.

> > Does anyone out there know a solution to this deceptively simple task.

> > Thanks in advance

> > Phil Cook

--

Dave Peterson