Reducing A Number By 1 (one)

[Chief]

I have a cell range named CNT. Initially a number is manually entered in CNT
for the number of rows to be added at the cursor's position. Let's say that
number is 2 (two). As the machine adds rows, I want it to decrease the value
of CNT by 1 (one) until the value of CNT equals zero (0). I do not know how
to write an Excel macro line to do this. If it were a Lotus 1-2-3 macro,
that line would be, {let cnt;cnt-1}. My macro's end result is a circular
reference and the value of CNT is 0 (zero) after its first itineration.
Below is my macro.

Sub Macro1()
' Inserts rows until CNT value equals zero
Do Until Range ("CNT") = 0
If Range ("CNT") >= 1 Then
ActiveCell.Select
Selection.EntireRow.Insert
Range ("CNT").Formula ="=(CNT)-1" '<-- My incorrect line
End If
Loop
End Sub

Thank you very much for your help.

 

[William]

Hi Chief

Try it this way - the code inserts "x" number of rows from the active cell
where "x" is the number entered in range "CNT". I hope I've interpreted you
post accurately.

Sub test()
Range(ActiveCell, ActiveCell.Offset(Range("CNT"), 0)). _
EntireRow.Insert Shift:=xlDown
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| I have a cell range named CNT. Initially a number is manually entered in
CNT
| for the number of rows to be added at the cursor's position. Let's say
that
| number is 2 (two). As the machine adds rows, I want it to decrease the
value
| of CNT by 1 (one) until the value of CNT equals zero (0). I do not know
how
| to write an Excel macro line to do this. If it were a Lotus 1-2-3 macro,
| that line would be, {let cnt;cnt-1}. My macro's end result is a circular
| reference and the value of CNT is 0 (zero) after its first itineration.
| Below is my macro.
|
| Sub Macro1()
| ' Inserts rows until CNT value equals zero
| Do Until Range ("CNT") = 0
| If Range ("CNT") >= 1 Then
| ActiveCell.Select
| Selection.EntireRow.Insert
| Range ("CNT").Formula ="=(CNT)-1" '<-- My incorrect line
| End If
| Loop
| End Sub
|
| Thank you very much for your help.
| --
| Rodney Getschman
| Wisconsin VFW

 

[Chief]

Hello William
That's a really neat way of "doing the thing". But, your macro inserts one
row too many. Would you please rewrite it for me to only insert the number
of rows commensurate with the number entered in the cell named CNT? Thanks
again.

 

[William]

Hi Chief

Sub test()
Range(ActiveCell, ActiveCell.Offset(Range("CNT") - 1, 0)). _
EntireRow.Insert Shift:=xlDown
End Sub

--
XL2002
Regards

William

(e-mail address removed)

| Hello William
| That's a really neat way of "doing the thing". But, your macro inserts
one
| row too many. Would you please rewrite it for me to only insert the
number
| of rows commensurate with the number entered in the cell named CNT?
Thanks
| again.
|
|
| "William" wrote:
|
| > Hi Chief
| >
| > Try it this way - the code inserts "x" number of rows from the active
cell
| > where "x" is the number entered in range "CNT". I hope I've interpreted
you
| > post accurately.
| >
| > Sub test()
| > Range(ActiveCell, ActiveCell.Offset(Range("CNT"), 0)). _
| > EntireRow.Insert Shift:=xlDown
| > End Sub
| >
| > --
| > XL2002
| > Regards
| >
| > William
| >
| > (e-mail address removed)
| >
| > | > | I have a cell range named CNT. Initially a number is manually entered
in
| > CNT
| > | for the number of rows to be added at the cursor's position. Let's
say
| > that
| > | number is 2 (two). As the machine adds rows, I want it to decrease
the
| > value
| > | of CNT by 1 (one) until the value of CNT equals zero (0). I do not
know
| > how
| > | to write an Excel macro line to do this. If it were a Lotus 1-2-3
macro,
| > | that line would be, {let cnt;cnt-1}. My macro's end result is a
circular
| > | reference and the value of CNT is 0 (zero) after its first
itineration.
| > | Below is my macro.
| > |
| > | Sub Macro1()
| > | ' Inserts rows until CNT value equals zero
| > | Do Until Range ("CNT") = 0
| > | If Range ("CNT") >= 1 Then
| > | ActiveCell.Select
| > | Selection.EntireRow.Insert
| > | Range ("CNT").Formula ="=(CNT)-1" '<-- My incorrect line
| > | End If
| > | Loop
| > | End Sub
| > |
| > | Thank you very much for your help.
| > | --
| > | Rodney Getschman
| > | Wisconsin VFW
| >
| >
| >

 

[Dana DeLouis]

Just another way by putting EntireRow first. Inserting an entire row
assumes "xlDown."

ActiveCell.EntireRow.Resize([CNT]).Insert
'or
ActiveCell.EntireRow.Resize([CNT] - 1).Insert

HTH
Dana DeLouis