Calculate Net Salary

I would like to calculate net pay for employees using below code.

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

Not sure about some of the logic, but I think being 100% explicit when specifying the worksheet names will help

Code:

---

Option Explicit

Private Sub CommandButton1_Click()
Dim i As Long
Dim endRow As Long
Dim LastRow As Long
Dim wsRecoveries As Worksheet
Dim wsEarn As Worksheet

Set wsRecoveries = Worksheets("Sheet2")
Set wsEarn = Worksheets("Sheet1")

LastRow = wsRecoveries.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To LastRow
    'Calulate Office recovery Total
    wsRecoveries.Range("R" & i) = WorksheetFunction.Sum(wsRecoveries.Range("C" & i & ":Q" & i))
    'Calulate Outside recovery Total
    wsRecoveries.Range("AG" & i) = WorksheetFunction.Sum(wsRecoveries.Range("S" & i & ":AF" & i))
   
    'Calulate Total recovery Total
    wsRecoveries.Range("AH" & i).Value = wsRecoveries.Cells(i, 18) + wsRecoveries.Cells(i, 33)
Next i

'Calculate Net pay
endRow = wsEarn.Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To endRow
    wsEarn.Range("AI" & i).Value = wsRecoveries.Cells(i, 16) - wsEarn.Cells(i, 34)
Next i
End Sub

---

Thanks Paul its work fine

Did you follow a template to create these calculation methods? I started writing a code to calculate the net income of my father's company employees, but I don't know if I used the correct formulas. I found on the internet some blogs that offer tips and tricks about payment systems, including ThePayStubs. But I'm still curious what else other programmers use. By the way, you did a good job! In the future, name the functions more explicitly. It will be easier for you to read the code when you return to it.

Quote:

---

Originally Posted by Amymac

Did you follow a template to create these calculation methods? I started writing a code to calculate the net income of my father's company employees, but I don't know if I used the correct formulas. I found on the internet some blogs that offer tips and tricks about payment systems, including ThePayStubs. But I'm still curious what else other programmers use. By the way, you did a good job! In the future, name the functions more explicitly. It will be easier for you to read the code when you return to it.

---

1. Welcome to the forum. Please take a few minutes and read the FAQ in my sig

2. This is a 5-6 year old post that you replied to

3. I vaguely recall that I just modified the OP's initial logic

4. The OP added a Command Button to the WS names "CommandButton1" and the click event suffixes the name with "_Click"

The OP could call the CB anything they want so long as the sub is named "WhatEverTheyWant_Click"