sujittalukde
07-10-2008, 01:27 AM
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
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