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

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

Insert a Row - Variable Location
 

It is telling me:

Run-time error '1004':
Method 'Range' of object '_Worksheet' failed

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.

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

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

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

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

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

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

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

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