Problem for salary calculation

[malarvel]

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]

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]

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]

Place cursor inside code

Press F8 until error happens

What line is Yellow?

[Paul_Hossler]

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