where is the mistake?

[Guest]

I know my programming level is low and sure the mistake is a begginers
mistake...I want to insert a row if the number in row i+1 is bigger than
number than number in row i.

The code I've written is this:

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
If Worksheets("sheet1").Range("B" & i).Value <
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows("i:i").Insert Shift:=xlDown
End If
Next
End Sub


I can't find the mistake, can you help me????

Thanks!

 

[Guest]

I suppose you want something like this:

Dim i As Integer
Range("B2").Select
For i = 2 To 10
If Worksheets("sheet1").Range("B" & ActiveCell.Row).Value < _
Worksheets("sheet1").Range("B" & ActiveCell.Row + 1).Value Then
Rows(ActiveCell.Row + 1).Insert Shift:=xlDown
Range("B" & ActiveCell.Row + 2).Select
End If
Next

Regards,
Stefi

„xavi garriga†ezt írta:

 

[Guest]

Stefi,

Did you not mean to start in Row 1?

Range("B1").Select

Mike

 

[Guest]

Hi Mike,

I supposed a header in Row 1, if there is none, you are right, of course!

Regards,
Stefi


„Mike H†ezt írta:

 

[Guest]

SAhh I never thought of that I simply had the numbers 1 to 10 in 10 rows.

Mike

 

[Dave Peterson]

If you're inserting or deleting rows, it makes the code much easier to write and
understand if you start at the bottom and work your way to the top.

I think that this does what you want:

Option Explicit
Private Sub CommandButton1_Click()
Dim i As Long
For i = 10 To 1 Step -1
If Me.Range("B" & i).Value _
< Me.Range("B" & i + 1).Value Then
Me.Rows(i + 1).Insert
End If
Next i
End Sub

I assumed that the code is associated with a commandbutton from the control
toolbox toolbar placed on the worksheet that has the data.

The Me keyword refers to that worksheet that owns the commandbutton and code.

 

[Sandy Mann]

To answer your question, the problem is that the range you are working on is
expanding as the FOR/NEXT loop progresses.

On the first pass when i = 1, if B2 is larger than B1, then you are
inserting a new Row i, (incidentally that should be Rows(i) not Rows("ii"),
Ranges like Range("B2") should be in quotes but numbers, as you did in
Range("B" & i) should not.)

If you select Row1 and the manually insert a new Row the Row will be
inserted *above* the existing Row1. So this is exactly what your code, [if
the Rows("ii") was corrected], would do. When i is indexed up to 2 it now
checks B2 which was B1 until you inserted a new row so it is carrying out
the same test with the same result!. You therefore end up inserting 10 new
rows above the existing data.

Changing your code line to:
Rows(i + 1).Insert Shift:=xlDown
would work on the first pass and a new Row 2 would be inserted, however,
when the second pass is made we are now comparing the newly inserted blank
Row with one with data in it and no matter how small the number is, the
blank cell above it will *always* be smaller. Another new Row 2 will be
inserted and we are back into the same position that we were in the previous
scenario.

You *could* add a line after the line:
Rows(i + 1).Insert Shift:=xlDown
to say:
i = i + 1
This would cause the referenced Row to jump past the inserted Row and get
over the above problem. However, changing the counting variable during the
loop is a VERY BAD idea and in any case will not do what you want. It would
work for the first 5 rows but by that time i would be reading 10, ( 5
indexed by Next and 5 increased by i = i + 1). The second set of 5 cells in
Column B would not be tested.

One way to go is to not use the counting variable as a Row reference as
Stefi did or, using as much as your original code as possible, something
like:

Sub Test()
Dim i As Integer
i = 1
Do Until Worksheets("sheet1").Range("B" & i).Value = ""

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If
i = i + 1
Loop
End Sub

Which will add rows until it finds an empty cell. If there may be data in
the [original] Row 11 then you would have to change it to something like:

Sub Test2()
Dim i As Integer
Dim cCount As Integer
i = 1
cCount = 1
Do Until cCount = 10

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If

i = i + 1
cCount = cCount + 1
Loop
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Guest]

Thanks to all, but still appears an error to debug, the problem appears when
the macro has to insert a new row. appears a message error: '1004...'

It also appears a message error when I try to insert a row manually(I
neither can insert manually):
"To prevent possible lost of data, Microsof etxcel cannot shift nonblank
cells off the worksheet.

Try to delete or clear the cells to the roght and below your data. Then
select A! and save your workbook to reset the last cell used.

Or you can move the data to a new location and try again".


Previously to insert a row, the macro does the following actions:

1)creates a column with numbers depending on a criteria--- I've used select
case
2)Hides some columns
3)sort by two criterias


Thanks for all
--
atrep

To answer your question, the problem is that the range you are working on is
expanding as the FOR/NEXT loop progresses.

On the first pass when i = 1, if B2 is larger than B1, then you are
inserting a new Row i, (incidentally that should be Rows(i) not Rows("ii"),
Ranges like Range("B2") should be in quotes but numbers, as you did in
Range("B" & i) should not.)

If you select Row1 and the manually insert a new Row the Row will be
inserted *above* the existing Row1. So this is exactly what your code, [if
the Rows("ii") was corrected], would do. When i is indexed up to 2 it now
checks B2 which was B1 until you inserted a new row so it is carrying out
the same test with the same result!. You therefore end up inserting 10 new
rows above the existing data.

Changing your code line to:
Rows(i + 1).Insert Shift:=xlDown
would work on the first pass and a new Row 2 would be inserted, however,
when the second pass is made we are now comparing the newly inserted blank
Row with one with data in it and no matter how small the number is, the
blank cell above it will *always* be smaller. Another new Row 2 will be
inserted and we are back into the same position that we were in the previous
scenario.

You *could* add a line after the line:
Rows(i + 1).Insert Shift:=xlDown
to say:
i = i + 1
This would cause the referenced Row to jump past the inserted Row and get
over the above problem. However, changing the counting variable during the
loop is a VERY BAD idea and in any case will not do what you want. It would
work for the first 5 rows but by that time i would be reading 10, ( 5
indexed by Next and 5 increased by i = i + 1). The second set of 5 cells in
Column B would not be tested.

One way to go is to not use the counting variable as a Row reference as
Stefi did or, using as much as your original code as possible, something
like:

Sub Test()
Dim i As Integer
i = 1
Do Until Worksheets("sheet1").Range("B" & i).Value = ""

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If
i = i + 1
Loop
End Sub

Which will add rows until it finds an empty cell. If there may be data in
the [original] Row 11 then you would have to change it to something like:

Sub Test2()
Dim i As Integer
Dim cCount As Integer
i = 1
cCount = 1
Do Until cCount = 10

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If

i = i + 1
cCount = cCount + 1
Loop
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Dave Peterson]

XL: "Cannot Shift Objects Off Sheet" Error Hiding Columns
http://support.microsoft.com/default.aspx?scid=kb;en-ca;211769

Remember to look for comments and merged cells.

Thanks to all, but still appears an error to debug, the problem appears when
the macro has to insert a new row. appears a message error: '1004...'

It also appears a message error when I try to insert a row manually(I
neither can insert manually):
"To prevent possible lost of data, Microsof etxcel cannot shift nonblank
cells off the worksheet.

Try to delete or clear the cells to the roght and below your data. Then
select A! and save your workbook to reset the last cell used.

Or you can move the data to a new location and try again".

Previously to insert a row, the macro does the following actions:

1)creates a column with numbers depending on a criteria--- I've used select
case
2)Hides some columns
3)sort by two criterias

Thanks for all
--
atrep

To answer your question, the problem is that the range you are working on is
expanding as the FOR/NEXT loop progresses.

On the first pass when i = 1, if B2 is larger than B1, then you are
inserting a new Row i, (incidentally that should be Rows(i) not Rows("ii"),
Ranges like Range("B2") should be in quotes but numbers, as you did in
Range("B" & i) should not.)

If you select Row1 and the manually insert a new Row the Row will be
inserted *above* the existing Row1. So this is exactly what your code, [if
the Rows("ii") was corrected], would do. When i is indexed up to 2 it now
checks B2 which was B1 until you inserted a new row so it is carrying out
the same test with the same result!. You therefore end up inserting 10 new
rows above the existing data.

Changing your code line to:
Rows(i + 1).Insert Shift:=xlDown
would work on the first pass and a new Row 2 would be inserted, however,
when the second pass is made we are now comparing the newly inserted blank
Row with one with data in it and no matter how small the number is, the
blank cell above it will *always* be smaller. Another new Row 2 will be
inserted and we are back into the same position that we were in the previous
scenario.

You *could* add a line after the line:
Rows(i + 1).Insert Shift:=xlDown
to say:
i = i + 1
This would cause the referenced Row to jump past the inserted Row and get
over the above problem. However, changing the counting variable during the
loop is a VERY BAD idea and in any case will not do what you want. It would
work for the first 5 rows but by that time i would be reading 10, ( 5
indexed by Next and 5 increased by i = i + 1). The second set of 5 cells in
Column B would not be tested.

One way to go is to not use the counting variable as a Row reference as
Stefi did or, using as much as your original code as possible, something
like:

Sub Test()
Dim i As Integer
i = 1
Do Until Worksheets("sheet1").Range("B" & i).Value = ""

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If
i = i + 1
Loop
End Sub

Which will add rows until it finds an empty cell. If there may be data in
the [original] Row 11 then you would have to change it to something like:

Sub Test2()
Dim i As Integer
Dim cCount As Integer
i = 1
cCount = 1
Do Until cCount = 10

If Worksheets("sheet1").Range("B" & i).Value < _
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows(i + 1).Insert Shift:=xlDown
i = i + 1
End If

i = i + 1
cCount = cCount + 1
Loop
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

I know my programming level is low and sure the mistake is a begginers
mistake...I want to insert a row if the number in row i+1 is bigger than
number than number in row i.

The code I've written is this:

Private Sub CommandButton1_Click()
Dim i As Integer
For i = 1 To 10
If Worksheets("sheet1").Range("B" & i).Value <
Worksheets("sheet1").Range("B" & i + 1).Value Then
Rows("i:i").Insert Shift:=xlDown
End If
Next
End Sub


I can't find the mistake, can you help me????

Thanks!