malarvel
08-13-2016, 02:34 AM
I have a data in sheet1. I have calculate the salary for all the employee using below code
Private Sub CommandButton1_Click()
Dim i As Integer
Dim totalpay As Double
'Calculate D.A & H.R.A
lastrow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To lastrow
Cells(i, 11) = Round((Cells(i, 10) * Cells(4, 22)), 0)
Cells(i, 12) = Round((Cells(i, 10) * Cells(5, 22)), 0)
'Calculate T.A
Select Case Sheets("sheet1").Cells(i, 8)
Case 1800 Or 1900
Cells(i, 13) = 900 + Round(900 * Cells(i, 22), 0)
Case 2000 Or 2200 Or 2400 Or 2800 Or 4000 Or 4200 Or 4800
Cells(i, 13) = 1800 + Round(1800 * Cells(i, 22), 0)
Case Is >= 5400
Cells(i, 13) = 3600 + Round(3600 * Cells(i, 22), 0)
End Select
totalpay = (Cells(i, 10) + Cells(i, 11) + Cells(i, 12) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15))
Cells(i, 16) = totalpay
Next i
End Sub
In the above code there is no problem for calculating D.A & H.R.A and only problem for calculating T.A for all the employee.
Procedure for calculating T.A is:
1. if grade pay 1800 to 1900 then the T.A is =900+D.A RATE
2. if grade pay 2000 to 4200 then the T.A is =1800+D.A RATE
2. if grade pay 5400 and above then the T.A is =3600+D.A RATE
Kindly give the solution for calculating T.A
Private Sub CommandButton1_Click()
Dim i As Integer
Dim totalpay As Double
'Calculate D.A & H.R.A
lastrow = Sheets("sheet1").Range("A" & Rows.Count).End(xlUp).Row
For i = 5 To lastrow
Cells(i, 11) = Round((Cells(i, 10) * Cells(4, 22)), 0)
Cells(i, 12) = Round((Cells(i, 10) * Cells(5, 22)), 0)
'Calculate T.A
Select Case Sheets("sheet1").Cells(i, 8)
Case 1800 Or 1900
Cells(i, 13) = 900 + Round(900 * Cells(i, 22), 0)
Case 2000 Or 2200 Or 2400 Or 2800 Or 4000 Or 4200 Or 4800
Cells(i, 13) = 1800 + Round(1800 * Cells(i, 22), 0)
Case Is >= 5400
Cells(i, 13) = 3600 + Round(3600 * Cells(i, 22), 0)
End Select
totalpay = (Cells(i, 10) + Cells(i, 11) + Cells(i, 12) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15))
Cells(i, 16) = totalpay
Next i
End Sub
In the above code there is no problem for calculating D.A & H.R.A and only problem for calculating T.A for all the employee.
Procedure for calculating T.A is:
1. if grade pay 1800 to 1900 then the T.A is =900+D.A RATE
2. if grade pay 2000 to 4200 then the T.A is =1800+D.A RATE
2. if grade pay 5400 and above then the T.A is =3600+D.A RATE
Kindly give the solution for calculating T.A