For loop - update your expression to evaluate

[Michael_Randall]

Hello,

I am using a for loop to loop through the cells in a column until the last
row. When I hit a cell where the value changes, I want to insert a row above
that. This will cause the evaluation expression to not be correct. I have
tried a few different things, but nothing allows me to update that expression
after I insert a new row. So my loop stops short. I'm am attaching my code
for review. My code may not be the most efficient, because I'm just starting
and learning about Objects.

Sub NewImprovedAddRows()

Dim curCellCol As Integer
Dim curCellRow As Integer
Dim curCellVal As Integer
Dim curCellAddr As String

Dim nextCellCol As Integer
Dim nextCellRow As Integer
Dim nextCellVal As Integer
Dim nextCellAddr As String

Dim i As Integer
For i = 1 To ActiveSheet.Cells(ActiveSheet.Rows.Count, "A").End(xlUp).Row
Cells(i, "A").Activate
curCellVal = ActiveCell.Value
curCellRow = ActiveCell.Row
nextCellRow = Cells(i + 1, "A").Row
nextCellVal = Cells(i + 1, "A").Value
nextCellAddr = Cells(i + 1, "A").Address


If IsNull(nextCellVal) Then
MsgBox ("The next cell is empty")

ElseIf nextCellVal <> curCellVal Then

Range(nextCellAddr, Range(nextCellAddr).End(xlToRight)).Activate

ActiveCell.EntireRow.Insert

i = ActiveCell.Row
Else
MsgBox ("help" & curCellRow & ", " & curCellVal & ", " & nextCellRow &
", " & nextCellVal)

End If

Next

End Sub

 

[Mike H]

Hi,

Try this

Sub insertrowifvalechg()
'insert a row at every name change in a column
Set sht = Sheets("Sheet1") ' change to suit
MyColumn = "A"
Dim x As Long
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
For x = Cells(Rows.Count, MyColumn).End(xlUp).Row To 2 Step -1
If Cells(x - 1, MyColumn) <> Cells(x, MyColumn) Then Rows(x).Insert
Next x
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

--
Mike

When competing hypotheses are otherwise equal, adopt the hypothesis that
introduces the fewest assumptions while still sufficiently answering the
question.