Seems to me you're offsetting from a sheet and not from a range.

Thus change to:

Sheets("Quote").Range("insertpoint").Offset(-5,0).value =
Range("Insertpoint").Offset(-11,0).Value + 1

On the other hand, you told us you wanted the number in the new range,
so don't you mean ...Range("insertpoint").Offset(1,0) ?

Also it seems logical to use With ... End With

Sub addrow()

Sheets("Row Template").Range("A1:G6").Copy
With Sheets("Quote").Range("insertpoint")
.Insert Shift=xlDown
.Offset(-5,0).Value = . Offset(-11,0)Value + 1
End With
Sheets("Quote").PageSetup.PrintArea =
"$A$1:(offset(insertpoint,1,6))"

End Sub


On Mon, 29 Dec 2003 10:16:40 -0600, cornishbloke
wrote:

Just when I thought I was getting better at using VBA I get stuck on
something that I'm sure should be simple :( :

in my spreadsheet I have a macro called "addrow" which adds a bordered
area of 6 rows and 7 columns to the bottom of the current table. This
macro uses a cell named "insertpoint" so that it knows where to insert
the copied area.

The First area covers A2:G7, the second covers A8:G13 and so on. Each
existing area is numbered in it's second row, in column A (thus the
A2:G7 area is numbered "1" in cell A3, the A8:G13 area is numbered "2"
in cell A9 etc).

All I want to know is, how do I modify the existing code to
automatically enter this number in a newly added area? I know it
should be a case of using offset to find the last used value and the
cell in which to place this same value +1, but how???

The code so far is:-

Sub addrow()
'
Sheets("Row Template").Range("A1:G6").Copy
Sheets("Quote").Range("insertpoint").Insert Shift:=xlDown
Sheets("Quote").PageSetup.PrintArea =
"$A$1:(offset(insertpoint,1,6))"

End Sub

I think it should be change to something like:-

Sub addrow()
'
Sheets("Row Template").Range("A1:G6").Copy
Sheets("Quote").Range("insertpoint").Insert Shift:=xlDown
Sheets("Quote").offset(insertpoint,-5,0).value =
(offset(insertpoint,-11,0).value)+1
Sheets("Quote").PageSetup.PrintArea =
"$A$1:(offset(insertpoint,1,6))"

End Sub


..but this produces the error "sub or function not defined". Am I
making a syntax error??? HELP!


---
Message posted from http://www.ExcelForum.com/