Increment upper bound of loop

[sujittalukde]

I am using the following code to insert a row and the put total to that row by looping procedure.


Sub maketotaltochallan()
Sheets("Challan").Select
itax = 0
sc = 0
ecess = 0
tottax = 0
startrow = 2
lastrow = Range("A65536").End(xlUp).Row 'ActiveSheet.UsedRange.Rows.Count
lastval = Range("B" & startrow).Value + Range("C" & startrow).Value
For i = startrow To lastrow
Range("A" & startrow).Select
curval = Range("B" & startrow).Value + Range("C" & startrow).Value
If lastval = curval Then
itax = itax + ActiveCell.Offset(0, 4).Value
sc = sc + ActiveCell.Offset(0, 5).Value
ecess = ecess + ActiveCell.Offset(0, 6).Value
tottax = tottax + ActiveCell.Offset(0, 9).Value
End If
If lastval <> curval Then
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(0, 4).Value = itax
ActiveCell.Offset(0, 5).Value = sc
ActiveCell.Offset(0, 6).Value = ecess
ActiveCell.Offset(0, 9).Value = tottax
itax = 0
sc = 0
ecess = 0
tottax = 0
End If
lastval = curval
startrow = startrow + 1

Next i
End Sub

The code is taking the last row value before the start of loop.

Now whenever the code inserts a new row the lastrow value changes. How can I change the last row number sp that the code can loop upto the last fil

[RonMcK]

sujittalukde,

I changed your two IF-THENs into one IF-THEN-ELSE and used 'i' instead of 'startrow' to identify the current row within the FOR-NEXT loop. Sub maketotaltochallan()
Sheets("Challan").Select
itax = 0
sc = 0
ecess = 0
tottax = 0
startrow = 2
lastrow = Range("A65536").End(xlUp).Row 'ActiveSheet.UsedRange.Rows.Count
lastval = Range("B" & startrow).Value + Range("C" & startrow).Value
For i = startrow To lastrow
Range("A" & i).Select
curval = Range("B" & i).Value + Range("C" & i).Value
If lastval = curval Then
itax = itax + ActiveCell.Offset(0, 4).Value
sc = sc + ActiveCell.Offset(0, 5).Value
ecess = ecess + ActiveCell.Offset(0, 6).Value
tottax = tottax + ActiveCell.Offset(0, 9).Value
Else
ActiveCell.EntireRow.Insert Shift:=xlDown
ActiveCell.Offset(0, 4).Value = itax
ActiveCell.Offset(0, 5).Value = sc
ActiveCell.Offset(0, 6).Value = ecess
ActiveCell.Offset(0, 9).Value = tottax
itax = 0
sc = 0
ecess = 0
tottax = 0
lastrow = lastrow + 1 ' added to push the end out each time a row is inserted
End If
lastval = curval
' startrow = startrow + 1 'not needed since formulas changed to use 'i' instead of 'startrow'
Next i
End Sub

HTH,

[sujittalukde]

Hi
Thanks for the reply
Still it is not inserting total from row 200 onwards.

At row 200, a break is required.

A file is attached.

Just for testing purpose, when I manually set lastrow = 250 (or more) it loops and inserts row break at row 200 and also after the last row ie at the end of table. But I need to set the last row through code.