Do Until... Looping Issues

[Skill<Ambition]

Right out of the gate let me start by saying I’m no pro at this stuff. I’m really just a beginner with lofty ambitions and incremental growth in skill… Hence the post here… </SPAN>

My Problem:</SPAN>

I have a data set of an indeterminate amount of rows. It can be large. It can be small. It all depends on how long (date) the report range is. </SPAN>

The data comes back in rows—if you didn’t catch that earlier—with column headers. </SPAN>

My task is inserting a blank (empty) row between each row of data and for Columns “J” through “BB” calculate the difference between the cell above and the cell below the newly created empty cell/row. If the value is < > “0.00” it should highlight the cell Yellow. </SPAN>

I want this task to continue to the bottom of the data set in that column, and all the way to column “BB.” </SPAN>

The issue I’m running into is that the loop is stopping on the first empty cell in one of the new rows I’ve created. I’m not sure how to get it to loop to the bottom of the column where there is actually no more data. </SPAN>

Any thoughts? Code is copied below. No pocking fun of me… I’m just a newbie. I’m sure this code looks like crap… </SPAN>

'Adding New Rows
Dim r As Integer, Count As Integer
Application.ScreenUpdating = False
Count = Range("a65536").End(xlUp).Row
For r = Count To 1 Step -1 ' work backwards to account for added lines
If Cells(r, 1) <> vbNullString Then ' skip blank lines
ActiveSheet.Rows(r + 1).Insert
End If
Next r
Application.ScreenUpdating = True

'Hidden Top Row (Row 2)--to keep it from cacl the column header with a number value
Rows("2:2").Select
Selection.EntireRow.Hidden = True

'Differences Calc
Range("j4").Select
Do Until ActiveCell.Value = ""
Dim i As Currency
i = ActiveCell.Select
ActiveCell.FormulaR1C1 = "=R[1]C-R[-1]C"
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlGreater, _
Formula1:="0.00"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlLess, _
Formula1:="0.00"
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False

ActiveCell.Offset(2, 0).Select
Loop
End Sub

 

[pplstuff]

dim iLastRow as long
dim iRow as long
dim rCell as range

ilastrow = cells(rows.count, 1).end(xlup).row

for irow = ilastrow to 2 step -1
rows(irow).insert
with activesheet
.range("J" & irow).formula = "=J" & irow - 1 & "-J" & irow + 1
.range("J" & irow & ":BB" & irow).fillright
application.calculate
for each rcell in .range("J" & irow & ":BB" & irow)
if rcell.value <> 0 then
rcell.interior.color = 65535
end if
next irow

This is untested, but it should... Loop through each row, insert a new row, fill a formula from column J to BB, is the value of any of those cells is not zero color them "65535" (not sure what color that is).

Note, the last row will be decided by column A. Hopefully you can adjust for your needs.

 

[pplstuff]

Random tip...

Always declare integers as a "long" variable type. As I understand, at runtime, VBA automatically converts all integers to long data types. So even though 'long' takes more bytes, your code should run faster since it doesn't have to convert all the integers.

 

[Skill<Ambition]

dim iLastRow as long
dim iRow as long
dim rCell as range

ilastrow = cells(rows.count, 1).end(xlup).row

for irow = ilastrow to 2 step -1
rows(irow).insert
with activesheet
.range("J" & irow).formula = "=J" & irow - 1 & "-J" & irow + 1
.range("J" & irow & ":BB" & irow).fillright
application.calculate
for each rcell in .range("J" & irow & ":BB" & irow)
if rcell.value <> 0 then
rcell.interior.color = 65535
end if
next irow

This is untested, but it should... Loop through each row, insert a new row, fill a formula from column J to BB, is the value of any of those cells is not zero color them "65535" (not sure what color that is).

Note, the last row will be decided by column A. Hopefully you can adjust for your needs.

This is awesome. looking at this I can clearly see who my skills need improvements. However, when i run it, i get the

Compile Error: Invalid Next Control Variagle Reference

when the code hits "next iRow"

And thank for the tip on doing it as Long and getting better performance.

 

[Skill<Ambition]

Any one else have any thoughts on why this is getting a compile error when it hits "Next irow"

It's like I'm almost there....

dim iLastRow as long
dim iRow as long
dim rCell as range

ilastrow = cells(rows.count, 1).end(xlup).row

for irow = ilastrow to 2 step -1
rows(irow).insert
with activesheet
.range("J" & irow).formula = "=J" & irow - 1 & "-J" & irow + 1
.range("J" & irow & ":BB" & irow).fillright
application.calculate
for each rcell in .range("J" & irow & ":BB" & irow)
if rcell.value <> 0 then
rcell.interior.color = 65535
end if
next irow

This is untested, but it should... Loop through each row, insert a new row, fill a formula from column J to BB, is the value of any of those cells is not zero color them "65535" (not sure what color that is).

Note, the last row will be decided by column A. Hopefully you can adjust for your needs.

 

[StephenCrump]

Any one else have any thoughts on why this is getting a compile error when it hits "Next irow"
It's like I'm almost there.... <ambition;3608204]any one="" else="" have="" any="" thoughts="" on="" why="" this="" is="" getting="" a="" compile="" error="" when="" it="" hits="" "next="" irow"

As a general principle, it's good practice to indent your code. Now, you can see more easily that you were missing </ambition;3608204]any>
an End With to close the With block, and a Next rCell to close the For each rCell<ambition;3608204]any one="" else="" have="" any="" thoughts="" on="" why="" this="" is="" getting="" a="" compile="" error="" when="" it="" hits="" "next="" irow"
loop. The code now does what I think you were looking to do:
</ambition;3608204]any>

Sub InsertRows()

    Dim iLastRow As Long
    Dim iRow As Long
    Dim rCell As Range
    
    iLastRow = Cells(Rows.Count, 1).End(xlUp).Row
    
    For iRow = iLastRow To 2 Step -1
        Rows(iRow).Insert
        With ActiveSheet
            .Range("J" & iRow).Formula = "=J" & iRow - 1 & "-J" & iRow + 1
            .Range("J" & iRow & ":BB" & iRow).FillRight
            Application.Calculate
            For Each rCell In .Range("J" & iRow & ":BB" & iRow)
                If rCell.Value <> 0 Then
                    rCell.Interior.Color = 65535
                End If
            Next rCell
        End With
    Next iRow

End Sub

Playing around with VBA solutions<ambition;3608204]any one="" else="" have="" any="" thoughts="" on="" why="" this="" is="" getting="" a="" compile="" error="" when="" it="" hits="" "next="" irow"
is </ambition;3608204]any>a great way to learn VBA, <ambition;3608204]any one="" else="" have="" any="" thoughts="" on="" why="" this="" is="" getting="" a="" compile="" error="" when="" it="" hits="" "next="" irow"
but perhaps not the most efficient way to solve the problem in this case?

Have you thought about using conditional formatting, rather than inserting new rows? For example, if you want to highlight cell A2 if it's not equal to A1, then conditional format A2 using a formula approach, i.e. to highlight the cell if this formula is true:

=A2<>A1

Or, to allow some tolerance for rounding differences:

</ambition;3608204]any>=ABS(A2-A1)>0.005 (say).
<ambition;3608204]any one="" else="" have="" any="" thoughts="" on="" why="" this="" is="" getting="" a="" compile="" error="" when="" it="" hits="" "next="" irow"

</ambition;3608204]any>You can copy this formatting to other cells, and relative referencing will be retained.

However, even if the cells are highlighted, you'll still need to scan through 45 columns and presumably many rows of data looking for differences. I'd be looking to summarise any anomalies in a single row. As you've described the problem, it sounds like you want all values in the columns to be identical? So you could, for example, simply compare MAX() of each column with MIN() of each column, and scan the column for data anomalies only if this test fails your condition.

Finally, just following up the earlier comment about data types: Integer and Long are two distinct data types. Both handle integers, but Integer is limited to the range -32,768 to 32,767. You may have code using Integer variables that runs happily for months, but breaks down when the variable first exceeds 32,767. For this reason, I always use Long for integer variables .. it saves thinking about whether the variable may ever exceed 32,767. The only downside is storage - Long takes 4 bytes vs Integer 2, but that's not an issue with modern computers. But if you want to use Integer for variables that you know will only be small, there's no problem doing so.