Adding a Row in a table cause an error

[dikken20]

Hello,

Why do I get an error on this part (located inside the IF block below) :

   Cells(RowCnt, 2).EntireRow.Insert = True

Sub Adding_ROW()
Application.ScreenUpdating = False
    BeginRow = 2
    EndRow = 600
    Inst_Name = Application.InputBox("Enter an Instrument Name")
'   Inst_Serial = Application.InputBox("Enter an Instrument Serial")


    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, 2).Value > Inst_Name Then
            Cells(RowCnt, 2).EntireRow.Insert = True
                     
        End If
    Next RowCnt
  
End Sub

 

[mart37]

remove: = True

 

[dikken20]

remove: = True

tried this already,
Also tried Range instead of Cells with both cases with and without the "True".
Still got this runtime error..

 

[Fluff]

What was the error message & number?

 

[dikken20]

What was the error message & number?

 

[Fluff]

Is the sheet protected?

 

[mart37]

I think that you need to stop the for next loop when a row is inserted. When not then the insert row shall repeat because the next row is the same as the last row before inserting and always greater than the comparative string.

 

[dikken20]

Is the sheet protected?

Nope, it is not protected.

I think that you need to stop the for next loop when a row is inserted. When not then the insert row shall repeat because the next row is the same as the last row before inserting and always greater than the comparative string.

I tried many variations and one was what you suggested, but it's not that, I tried two methods to not getting into that situation you described. First was the operator of the If from < to >, second method was setting the RowCnt to RowCnt+2..
No matter what I do I get this runtime error.


Just to clarify, the "If" statement is checking whether the string in the cell is greater than the Inst_Name variable, purpose is to add a Row in a sorted table, So I guess what I'm asking is that maybe the operator ">" is not the right operator to use in strings? I hardly think that since it does add an empty row just at the right place..

So again, I'm at the same place with that runTime error for some reason.
Any Ideas?

 

[mart37]

        If Cells(RowCnt, 2).Value > Inst_Name Then
            Cells(RowCnt, 2).EntireRow.Insert
            RowCnt = EndRow
        End If

 

[mart37]

There is an other problem. How to add a item after the last row.

Sub Adding_ROW()
    Application.ScreenUpdating = False
    Dim BeginRow As Long
    Dim EndRow As Long
    Dim RowCnt As Long
    Dim Inst_Name As String
    BeginRow = 2
    EndRow = Cells(Rows.Count, 2).End(xlUp).Row
    Inst_Name = Application.InputBox("Enter an Instrument Name")
    For RowCnt = BeginRow To EndRow
        If Cells(RowCnt, 2).Value > Inst_Name Then
            Cells(RowCnt, 2).EntireRow.Insert
            Cells(RowCnt, 2) = Inst_Name
            RowCnt = EndRow
        ElseIf RowCnt = EndRow Then
            Cells(RowCnt + 1, 2) = Inst_Name
        End If
    Next RowCnt
    Application.ScreenUpdating = True
End Sub