PDA

View Full Version : Problem for salary calculation



malarvel
09-13-2016, 06:54 AM
I have two sheets namely sheet1(employee) and sheet2(Earnings). In both sheets column A is identical (Empid). In sheet2(earnings) from colums(A) to G the data available. Column H to J I would go to calculate (D.A/HRA/TA) and column P is gross pay. In column V4 and V5 the rate of DA & HRA is respectively.

DA & HRA will be calculated based on column G (New Basic Pay) of sheet2(Earnings). TA will be calculated based on column H (Grade Pay) of Sheet1(Employee).

The procedure for TA calculation as follows.

If grade pay from 1800 To 1900 then
Sheet2(Earnings) column J= 900 + Round(900 * .cells(4, 22), 0)
If grade from 2000 To 4800 then
Sheet2(Earnings) column J = 1800 + Round(1800 * .cells(4, 22), 0)
If grade pay Is >= 5400
Sheet2(Earnings) column J = 3600 + Round(3600 * .cells(4, 22), 0).

I have a code below for reference, but when I execute the code its not working.



Private Sub CommandButton2_Click()
Dim ws1 As Worksheet, ws2 As Worksheet, x As Range
Dim i As Integer
Dim totalpay As Double

Set ws1 = Sheets("Employee")
Set ws2 = Sheets("Earnings")

With ws2
'Calculate D.A & H.R.A
For i = 2 To Range("A" & Rows.Count).End(xlUp).Row

Set x = ws1.Columns(1).Find(.cells(i, "A"), LookIn:=xlValues, lookat:=xlWhole)
If Not x Is Nothing Then

.Range (cells(i, 8) = Round((.cells(i, 7) * .cells(4, 22)), 0))
.Range(cells(i, 9)) = Round((.cells(i, 7) * .cells(5, 22)), 0)

'Calculate TA
Select Case ws1.cells(x, 8)
Case 1800 To 1900
.Range(cells(i, 10)) = 900 + Round(900 * .cells(4, 22), 0)

Case 2000 To 4800
.Range(cells(i, 10)) = 1800 + Round(1800 * .cells(4, 22), 0)
Case Is >= 5400
.Range(cells(i, 10)) = 3600 + Round(3600 * .cells(4, 22), 0)
End Select



totalpay = .Range(cells(i, 7) + .cells(i, 8) + .cells(i, 9) + .cells(i, 10) + .cells(i, 11) + .cells(i, 12) + .cells(i, 13) + .cells(i, 14) + .cells(i, 15))

.Range(cells(i, 16)) = totalpay
End If
Set x = Nothing
Next i
End With
MsgBox ("Salary Calculate Successfully")

End Sub



Kindly help for above task.

SamT
09-13-2016, 08:52 AM
Select Case ws1.cells(x, 8) x is a cell Not a Row number
Try
Select Case x
or Select Case Cells(x.Row, 8)
or Select Case x.Offset(, 8)

malarvel
09-13-2016, 10:52 PM
Sir

As per your suggestion I changed code to Select Case Cells(X.Row, 8) then I run the code again it wouldn't work, the error message (Invalid use of Property) will display.

awaiting your valuable reply.

SamT
09-14-2016, 07:17 AM
Place cursor inside code

Press F8 until error happens

What line is Yellow?

Paul_Hossler
09-14-2016, 08:22 AM
I think you have too many Cells() without the 'dot' and too many Range(Cell(())

I suggested some structure changes and using more meaningful variable names to make it easier to follow (for me at least)





Option Explicit
Private Sub CommandButton2_Click()

Dim wsEmployees As Worksheet, wsEarnings As Worksheet
Dim rEmployees As Range, rEarnings As Range
Dim rEmp As Range, rEarn As Range
Dim iEmpNum As Long
Dim DA_Rate As Double, HRA As Double

Application.ScreenUpdating = False


Set wsEmployees = Sheets("Employee")
Set rEmployees = wsEmployees.Cells(1, 1).CurrentRegion
Set wsEarnings = Sheets("Earnings")
Set rEarnings = wsEarnings.Cells(1, 1).CurrentRegion


DA_Rate = wsEarnings.Range("V4").Value
HRA = wsEarnings.Range("V5").Value



For Each rEarn In rEarnings.Rows

If rEarn.Row = 1 Then GoTo GetNextEarnings

iEmpNum = 0
On Error Resume Next
iEmpNum = Application.WorksheetFunction.Match(rEarn.Cells(1, 1).Value, rEmployees.Columns(1), 0)
On Error GoTo 0

If iEmpNum = 0 Then GoTo GetNextEarnings

Set rEmp = rEmployees.Rows(iEmpNum)

rEmp.Cells(1, 8) = Round(rEmp.Cells(1, 7) * DA_Rate)
rEmp.Cells(1, 9) = Round(rEmp.Cells(1, 7) * HRA)

Select Case rEmp.Cells(1, 8).Value
Case 1800 To 1900
rEmp.Cells(1, 10) = 900 + Round(900 * DA_Rate, 0)
Case 2000 To 4800
rEmp.Cells(1, 10) = 180 + Round(1800 * DA_Rate, 0)
Case Is >= 5400
rEmp.Cells(1, 10) = 3600 + Round(3600 * DA_Rate, 0)
End Select

rEarn.Cells(1, 16).Value = Application.WorksheetFunction.Sum(rEmp.Cells(1, 7).Resize(1, 9))

GetNextEarnings:
Next

Application.ScreenUpdating = True

MsgBox ("Salary Calculate Successfully")

End Sub