problem calculate salary

[malrvel]

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.

[/COLOR]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 cells(x.Row, 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
[COLOR=#333333]

Kindly help please.

 

[Joe4]

It would probably be helpful to see sample of what the data looks like.

You can post screen images, if you like, using the tools mentioned in Section B here: Guidelines for Forum Use

There is also a "Test Here" forum you can use to test posting with those tools first.