For Next loop stops at row 13840

MkDr100 · Jan 26, 2005

I have a vba that compares two columns of numbers. It loops through starting at row six and ending with the last row (in this case row 13867). It compares the values in each row and inserts rows if needed to align the numbers. This part works fine. The problem is that the For Next loop exits before it gets to the end, at row 13840. Even though my lastrow variable equals 13867. Any ideas on how to correct this?? I've run the macro on Excel 2002 and 2003 and I get the same results.

Thanks.

Zack Barresse · Jan 26, 2005

Hi there,

We'd need to see the code in order to make any kind of guess as to why you are getting the results you are. Care to post?

MkDr100 · Jan 26, 2005

'
' DECLARATION OF VARIABLES
 Dim strLastDay As String 'last day of previous month
 Dim strLastRow1 As Long 'last row row number
 Dim strLastRow2 As Long 'last row row number
 Dim h As Long
 Dim i As Long



 Sheets("Sheet 2").Activate
 Range("B6").Select
 'user to imput the last day of previous month to delete records
 strLastDay = InputBox("Enter the Last Day of the Previous Month" & _
 "(mmddyy format), then press OK or ENTER", "Last day of the previous month", _
 "103104")

 'delete last months records
 strLastRow1 = Range("B65536").End(xlUp).Row
 For h = strLastRow1 To 6 Step -1
 On Error Resume Next
 If Cells(h, 3) = strLastDay Then
 Range("B" & h, "F" & h).Select
 Selection.Delete Shift:=xlUp
 End If
 Next h


 strLastRow1 = Range("B65536").End(xlUp).Row
 Range("B6").Select
 'Comparison Loop
 For i = 6 To strLastRow1
 If Cells(i, "E") = Cells(i, "I") Then GoTo Skip
 If Cells(i, "E") = Cells(i + 1, "I") Then
 Range("B" & i, "F" & i).Select
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i + 1, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i).Select
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i, "E") = Cells(i + 2, "I") Then
 Range("B" & i, "F" & i).Select
 Selection.Insert Shift:=xlDown
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i + 2, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i).Select
 Selection.Insert Shift:=xlDown
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
Skip:
 strLastRow1 = Range("B65536").End(xlUp).Row
 Next i

 'Add Variance Column
 strLastRow2 = Range("G65536").End(xlUp).Row
 Range("K6").Select
 ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-2]"
 ActiveCell.Copy
 Range("K6:K" & strLastRow2).Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("B6").Select

End Sub


I hope I did this right.

jimbojones · Jan 26, 2005

Hi,

As stated above we really need to see the code. However, if you are inserting rows it sounds as though you may be setting the variable row number then inserting rows. Perhaps you need to work backwards from your max row (13867 in this case) and work back to row 1. This means that inserting rows doesn't impact on the number of rows left to trawl thruogh.

Not sure if that makes sense?

James

MkDr100 · Jan 26, 2005

I understand, but starting at the bottom won't work in the spreadsheet.

Zack Barresse · Jan 26, 2005

Let me guess, you're inserting around 27 rows? Am I close?

When inserting or deleting, it's best to loop from the bottom up. You are affecting the very numbers which are aiding your loop ( i ). Try this as your loop start ...

 'Comparison Loop
 For i = strLastRow1 To 6 Step -1

.. see if that helps. You could shorten your whole code slightly, so the entire procedure might look like this ...

Option Explicit

Sub foofoo()
 '
 ' DECLARATION OF VARIABLES
 Dim strLastDay As String 'last day of previous month
 Dim strLastRow1 As Long 'last row row number
 Dim strLastRow2 As Long 'last row row number
 Dim h As Long, i As Long

 Sheets("Sheet 2").Activate
 Range("B6").Select
 'user to imput the last day of previous month to delete records
 strLastDay = InputBox("Enter the Last Day of the Previous Month" & _
 "(mmddyy format), then press OK or ENTER", "Last day of the previous month", _
 "103104")
 strLastRow1 = Range("B65536").End(xlUp).Row
 For h = strLastRow1 To 6 Step -1
 On Error Resume Next
 If Cells(h, 3) = strLastDay Then
 Range("B" & h, "F" & h).Delete
 End If
 Next h
 strLastRow1 = Range("B65536").End(xlUp).Row
 For i = strLastRow1 To 6 Step -1
 If Cells(i, "E") = Cells(i, "I") Then GoTo Skip
 If Cells(i, "E") = Cells(i + 1, "I") Then
 Range("B" & i, "F" & i).Insert
 GoTo Skip
 End If
 If Cells(i + 1, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i).Insert
 GoTo Skip
 End If
 If Cells(i, "E") = Cells(i + 2, "I") Then
 Range("B" & i, "F" & i + 1).Insert
 GoTo Skip
 End If
 If Cells(i + 2, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i + 1).Insert
 GoTo Skip
 End If
Skip:
 Next i
 strLastRow2 = Range("G65536").End(xlUp).Row
 Range("K6:K" & strLastRow2).FormulaR1C1 = "=RC[-6]-RC[-2]"
 Range("B6").Select
End Sub

MkDr100 · Jan 27, 2005

Yeah, 27 rows are added. How'd you know that???

Unfortunately, starting from the bottom won't work because the first set of data (after deleting out prior month records) is still over 100 records longer than the second set of data.
What I tried, since there are so many prior month records being deleted, was to NOT reset the strLastRow after the prior month records are deleted. This worked with this data set, but I don't know if the next data set will delete more prior period records than it adds. Here is the modified code. I've remarked out two lines of code.

Any idea why it stoped at 13840? Thanks again for all your suggestions.

' DECLARATION OF VARIABLES
 Dim strLastDay As String 'last day of previous month
 Dim strLastRow1 As Long 'last row row number
 Dim strLastRow2 As Long 'last row row number
 Dim h As Long
 Dim i As Long



 Sheets("Sheet 2").Activate
 Range("B6").Select
 'user to imput the last day of previous month to delete records
 strLastDay = InputBox("Enter the Last Day of the Previous Month" & _
 "(mmddyy format), then press OK or ENTER", "Last day of the previous month", _
 "103104")

 'delete last months records
 strLastRow1 = Range("B65536").End(xlUp).Row
 For h = strLastRow1 To 6 Step -1
 On Error Resume Next
 If Cells(h, 3) = strLastDay Then
 Range("B" & h, "F" & h).Select
 Selection.Delete Shift:=xlUp
 End If
 Next h


 '*** strLastRow1 = Range("B65536").End(xlUp).Row
 Range("B6").Select
 'Comparison Loop
 For i = 6 To strLastRow1
 If Cells(i, "E") = Cells(i, "I") Then GoTo Skip
 If Cells(i, "E") = Cells(i + 1, "I") Then
 Range("B" & i, "F" & i).Select
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i + 1, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i).Select
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i, "E") = Cells(i + 2, "I") Then
 Range("B" & i, "F" & i).Select
 Selection.Insert Shift:=xlDown
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
 If Cells(i + 2, "E") = Cells(i, "I") Then
 Range("G" & i, "J" & i).Select
 Selection.Insert Shift:=xlDown
 Selection.Insert Shift:=xlDown
 GoTo Skip
 End If
Skip:
 '*** strLastRow1 = Range("B65536").End(xlUp).Row
 Next i

 'Add Variance Column
 strLastRow2 = Range("G65536").End(xlUp).Row
 Range("K6").Select
 ActiveCell.FormulaR1C1 = "=RC[-6]-RC[-2]"
 ActiveCell.Copy
 Range("K6:K" & strLastRow2).Select
 ActiveSheet.Paste
 Application.CutCopyMode = False
 Range("B6").Select

End Sub

Zack Barresse · Jan 27, 2005

=13867-13840

..

=27

It is off by the number of rows you insert. You NEED to come from the bottom up. If not, you are fighting a losing battle. Without seeing the spreadsheet, it is very hard for me (I'm a very visual person). Work on dong it like I said, from the bottom up, don't reset your number with every iteration. Maybe think of doing away with those current variables and just using the range row in the "For i =" line.

For Next loop stops at row 13840

MkDr100

New Member

Excel Facts

Zack Barresse

MrExcel MVP

MkDr100

New Member

jimbojones

Well-known Member

MkDr100

New Member

Zack Barresse

MrExcel MVP

MkDr100

New Member

Zack Barresse

MrExcel MVP

Similar threads

Forum statistics

Share this page

For Next loop stops at row 13840

New Member

Excel Facts

MrExcel MVP

New Member

Well-known Member

New Member

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock