Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
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$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 making a syntax error??? HELP -- Message posted from http://www.ExcelForum.com |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
try something like
ActiveCell.Offset(iOffsetRows, iOffsetColumns).Formula = "something" and adapt it for your needs "cornishbloke" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
I'm still stuck on this question I posted yesterday. Can anyone help???
--- Message posted from http://www.ExcelForum.com/ |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
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$1:(offset(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! --- Message posted from http://www.ExcelForum.com/ |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
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/ |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
offset problems
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 "cornishbloke" wrote in message ... Just when I thought I was getting better at using VBA I get stuck on something that I'm sure should be simple :( : 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:" & insertpoint.Offset(1, 6).Address" End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
OFFSET Problems | Excel Worksheet Functions | |||
Offset/COUNTA problems | Excel Worksheet Functions | |||
Compare Cell Values, Offset(-1,0), Offset(-1,-1), and xlFillDefaul | Excel Worksheet Functions | |||
Problems with offset/average formula | Excel Worksheet Functions | |||
Problems copying cells using offset and counta | Excel Discussion (Misc queries) |