Calculate Net Salary

[malarvel]

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

[Paul_Hossler]

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




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

[malarvel]

Thanks Paul its work fine

[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 (https://www.thepaystubs.com/). 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.

[Paul_Hossler]

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 (https://www.thepaystubs.com/). 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"