Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
I am trying to do the equivalent of this code, but variable:
Rows("546:546").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove (as provided by a Macro) However, I do not want to insert the row, everytime, at row 546. I want this number to be variable... equal to the row at Range(cell_range.Offset(cell_count + 2)) This range would correspond to "$A$546" at the default cell_count. However, this number, cell_count, will change and I want the row to always be in the correct location. So, I figured I could modify the macro code slightly, and used this: Rows(cell_range.Offset(cell_count + 2, 0)).Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove However, it did not work. I figure it is because I am saying: Rows("$A$546").Insert How do I use variables to create something equivalent to Rows("546:546")? Thanks |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
On May 22, 3:30*pm, baconcow
wrote: I am trying to do the equivalent of this code, but variable: Rows("546:546").Select Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove (as provided by a Macro) However, I do not want to insert the row, everytime, at row 546. I want this number to be variable... equal to the row at Range(cell_range.Offset(cell_count + 2)) This range would correspond to "$A$546" at the default cell_count. However, this number, cell_count, will change and I want the row to always be in the correct location. So, I figured I could modify the macro code slightly, and used this: Rows(cell_range.Offset(cell_count + 2, 0)).Insert _ * * Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove However, it did not work. I figure it is because I am saying: Rows("$A$546").Insert How do I use variables to create something equivalent to Rows("546:546")? Thanks Try Range(cell_range.Offset(cell_count + 2)).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
It is telling me:
Run-time error '1004': Method 'Range' of object '_Worksheet' failed |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
How come this code fails?
' insert row Range(cell_range.Offset(cell_count + 2)).Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove It says "Method 'Range' of object '_Worksheet' failed? Please, any help. I just want to insert a row underneath the one in the active cell. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
Here is my full code:
' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
Maybe this:
Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
Thank you! It worked. I had tried it without the Range, but, that was before
I tried using ActiveCell as well. I had everything you see here, except the "EntireRow" portion. Now, with the EntireRow portion, it seems to work. Thanks again!!! cell_range.Offset(cell_count + 2).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
I WANT TO DO THE SAME THING BUT i CAN'T GET IT TO WORK
This is my macro: insertrows Macro ' Macro recorded 5/30/2008 by kathy.m.beck ' 'cell_range.Offset(cell_count + 2)Select 'Range ("A1:N6004"). Select ActiveCell.Offset(1).EntireRow.Insert_ Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove I get this error message: Compile error syntax error Can you help? Thanks! "Dave Peterson" wrote: Maybe this: Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
Something I just noticed is that you missed the period before your first
"Select" 'cell_range.Offset(cell_count + 2).Select Put that in there "Kathy Beck" wrote: I WANT TO DO THE SAME THING BUT i CAN'T GET IT TO WORK This is my macro: insertrows Macro ' Macro recorded 5/30/2008 by kathy.m.beck ' 'cell_range.Offset(cell_count + 2)Select 'Range ("A1:N6004"). Select ActiveCell.Offset(1).EntireRow.Insert_ Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove I get this error message: Compile error syntax error Can you help? Thanks! "Dave Peterson" wrote: Maybe this: Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
Thanks for replying to me-i tried that but I'm still getting the same error
message-It's highlighting the bottom tow of my macro but I can't see what I'm missing "baconcow" wrote: Something I just noticed is that you missed the period before your first "Select" 'cell_range.Offset(cell_count + 2).Select Put that in there "Kathy Beck" wrote: I WANT TO DO THE SAME THING BUT i CAN'T GET IT TO WORK This is my macro: insertrows Macro ' Macro recorded 5/30/2008 by kathy.m.beck ' 'cell_range.Offset(cell_count + 2)Select 'Range ("A1:N6004"). Select ActiveCell.Offset(1).EntireRow.Insert_ Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove I get this error message: Compile error syntax error Can you help? Thanks! "Dave Peterson" wrote: Maybe this: Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
I just figured out and it works now I had a _ rather than a -
"baconcow" wrote: Something I just noticed is that you missed the period before your first "Select" 'cell_range.Offset(cell_count + 2).Select Put that in there "Kathy Beck" wrote: I WANT TO DO THE SAME THING BUT i CAN'T GET IT TO WORK This is my macro: insertrows Macro ' Macro recorded 5/30/2008 by kathy.m.beck ' 'cell_range.Offset(cell_count + 2)Select 'Range ("A1:N6004"). Select ActiveCell.Offset(1).EntireRow.Insert_ Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove I get this error message: Compile error syntax error Can you help? Thanks! "Dave Peterson" wrote: Maybe this: Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Insert a Row - Variable Location
You also need a space after the "Insert" and before the "_":
ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove "Kathy Beck" wrote: Thanks for replying to me-i tried that but I'm still getting the same error message-It's highlighting the bottom tow of my macro but I can't see what I'm missing "baconcow" wrote: Something I just noticed is that you missed the period before your first "Select" 'cell_range.Offset(cell_count + 2).Select Put that in there "Kathy Beck" wrote: I WANT TO DO THE SAME THING BUT i CAN'T GET IT TO WORK This is my macro: insertrows Macro ' Macro recorded 5/30/2008 by kathy.m.beck ' 'cell_range.Offset(cell_count + 2)Select 'Range ("A1:N6004"). Select ActiveCell.Offset(1).EntireRow.Insert_ Shift:=xlDown,CopyOrigin:=xlFormatFromLeftOrAbove I get this error message: Compile error syntax error Can you help? Thanks! "Dave Peterson" wrote: Maybe this: Range(cell_range.Offset(cell_count + 2, 0)).Select should be: cell_range.Offset(cell_count + 2, 0).Select baconcow wrote: Here is my full code: ' (Declarations) Dim cell_count As Long Dim add_amount As Long Dim cell_range As Range Private Sub cell_setup() cell_count = WorksheetFunction.CountA(Range("A8:A1000")) Set cell_range = Worksheets("Inventory").Range("A8") ' FYI, cell_count = 546 in my example End Sub Private Sub add_item_Click() Dim cell_row As Long Dim lastrow As Long Call cell_setup add_amount = cell_range.Offset(cell_count + 3, 1).Value ' insert row Range(cell_range.Offset(cell_count + 2, 0)).Select 'Range("A1:D10").Select ActiveCell.Offset(1).EntireRow.Insert _ Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove End Sub -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using a variable to point to a location | Excel Worksheet Functions | |||
Insert new Worksheet with name, and insert the sheet into itsalphabetical / numerical location | Excel Programming | |||
Set a range from a variable location | Excel Discussion (Misc queries) | |||
VBA - Passing Cell Location To Variable | Excel Programming | |||
using variable as cell location | Excel Programming |