malarvel
08-27-2016, 03:05 AM
I would like to calculate net pay for employees using below code.
Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim endRow As Integer
Dim LastRow As Integer
Dim WS As Object
Set WS = Application.WorksheetFunction
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LastRow
'Calulate Office recovery Total
Range("R" & i) = WorksheetFunction.Sum(Range("C" & i & ":Q" & i))
'Calulate Outside recovery Total
Range("AG" & i) = WorksheetFunction.Sum(Range("S" & i & ":AF" & i))
'Calulate Total recovery Total
Range("AH" & i).Value = Cells(i, 18) + Cells(i, 33)
'Calculate Net pay
endRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For j = 5 To endRow
Range("AI" & i).Value = Cells(j, 16) - Cells(i, 34)
Next j
Next i
End Sub
First I have to calculate office recovery total second I have calculate outside recovery total and final would calculate net pay.
When I run the code the net pay range will display with wrong value.
I have attached herewith my sample data for your reference.
How to correct my code
Private Sub CommandButton1_Click()
Dim i As Integer
Dim j As Integer
Dim endRow As Integer
Dim LastRow As Integer
Dim WS As Object
Set WS = Application.WorksheetFunction
LastRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LastRow
'Calulate Office recovery Total
Range("R" & i) = WorksheetFunction.Sum(Range("C" & i & ":Q" & i))
'Calulate Outside recovery Total
Range("AG" & i) = WorksheetFunction.Sum(Range("S" & i & ":AF" & i))
'Calulate Total recovery Total
Range("AH" & i).Value = Cells(i, 18) + Cells(i, 33)
'Calculate Net pay
endRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row
For j = 5 To endRow
Range("AI" & i).Value = Cells(j, 16) - Cells(i, 34)
Next j
Next i
End Sub
First I have to calculate office recovery total second I have calculate outside recovery total and final would calculate net pay.
When I run the code the net pay range will display with wrong value.
I have attached herewith my sample data for your reference.
How to correct my code