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 |
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. |
offset problems
I'm still stuck on this question I posted yesterday. Can anyone help???
--- Message posted from http://www.ExcelForum.com/ |
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 |
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/ |
offset problems
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 "Tomek" wrote in message ... 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 |
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/ |
All times are GMT +1. The time now is 01:29 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com