offset problems

[cornishbloke]

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

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

The First area covers A2:G7, the second covers A8:G13 and so on. Eac
existing area is numbered in it's second row, in column A (thus th
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 t
automatically enter this number in a newly added area? I know i
should be a case of using offset to find the last used value and th
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$1offset(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$1offset(insertpoint,1,6))"

End Sub


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

 

[Tomek]

try something like

ActiveCell.Offset(iOffsetRows, iOffsetColumns).Formula = "something"

and adapt it for your needs

 

[cornishbloke]

I'm still stuck on this question I posted yesterday. Can anyone help???

 

[Tomek]

try this
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:" & insertpoint.Offset(1, 6).Address"

End Sub

and put appropriate number of "
I always have problems with that so to avoid it I use one more variable:

Dim s as String
..
'some code here
..
s="$A$1:"
Sheets("Quote").PageSetup.PrintArea = s & insertpoint.Offset(1, 6).Address
..
'some code here

 

[Leo Elbertse]

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$1offset(insertpoint,1,6))"

End Sub

 

[Tomek]

I only changed the line after .PrintArea=, the preceding lines are unchanged
You should also replace

Sheets("Quote").offset(insertpoint,-5,0).value =

with something like
Range("insertpoint").offset(-5,0).value =
Range("insertpoint").offset(-11,0).value + 1
but check if it works, I didn't

 

[cornishbloke]

Thank you Leo and Tomek,

I'm still new to VBA and so sometimes even obvious mistakes don't stand
out to me - the problem was indeed that I didn't see I was offsetting
from the sheet and not the range. The code I've used (which works
perfectly) is as follows:-

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$1offset(insertpoint,1,6))"
'
'
End Sub

I only hope that as I learn more I about VBA I can contribute and help
others as much as this forum helps me!

Have a happy New Year!