calculate salary for specific record

mutmalar · Aug 16th 2016

Using below code I have calculate salary for all the record.

Code

[/COLOR][COLOR=#333333][FONT=Verdana]Private Sub calculatesalaryall()Dim i As Integer[/FONT]Dim totalpay As Double'Calculate D.A & H.R.Alastrow = Sheets("Earnings").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.ASelect Case Sheets("Earnings").Cells(i, 8)Case 1800 To 1900Cells(i, 13) = 900 + Round(900 * Cells(4, 22), 0)Case 2000 To 4800Cells(i, 13) = 1800 + Round(1800 * Cells(4, 22), 0)Case Is >= 5400Cells(i, 13) = 3600 + Round(3600 * Cells(4, 22), 0)End Selecttotalpay = (Cells(i, 10) + Cells(i, 11) + Cells(i, 12) + Cells(i, 13) + Cells(i, 14) + Cells(i, 15))Cells(i, 16) = totalpayNext iEnd Sub[/COLOR][COLOR=#333333][COLOR=#333333][/COLOR][/COLOR][COLOR=#333333]

[/COLOR]But some time i would like to calculate salary for particular record alone. Fox example i want to calculate salary for the sl.No 22 alone i.e range "A22:P.22". Using below code I have tried to calculate for the same, but i have got error. Kindly give the solution for the same.

Code

[/COLOR][FONT=Verdana]Private Sub individualsalary()[/FONT]Dim aRange As RangeDim totalpay As Double  On Error Resume Next  Set aRange = Application.InputBox(prompt:="Enter range", Type:=8)  If aRange Is Nothing Then    MsgBox "Operation Cancelled"  Else    aRange.Select 'Calculate D.A & H.R.A    Cells(aRange, 11) = Round((Cells(aRange, 10) * Cells(4, 22)), 0)    Cells(aRange, 12) = Round((Cells(aRange, 10) * Cells(5, 22)), 0)'Calculate T.ASelect Case Sheets("Earnings").Cells(aRange, 8)Case 1800 To 1900Cells(aRange, 13) = 900 + Round(900 * Cells(4, 22), 0)Case 2000 To 4800Cells(aRange, 13) = 1800 + Round(1800 * Cells(4, 22), 0)Case Is >= 5400Cells(aRange, 13) = 3600 + Round(3600 * Cells(4, 22), 0)End Selecttotalpay = (Cells(aRange, 10) + Cells(aRange, 11) + Cells(aRange, 12) + Cells(aRange, 13) + Cells(aRange, 14) + Cells(aRange, 15))Cells(aRange, 16) = totalpayEnd If[FONT=Verdana]End Sub[/FONT] [COLOR=#333333]

[/COLOR]

**pike** · Aug 17th 2016

Re: calculate salary for specific record

Hi mutmalar,
Welcome to the forum
You need to qualify the Row of the selected range
try,,,,

Code

Private Sub calculatesalaryall()
Dim i As Integer
Dim totalpay As Double




'Calculate D.A & H.R.A
lastrow = Sheets("Earnings").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("Earnings").Cells(i, 8)
Case 1800 To 1900
Cells(i, 13) = 900 + Round(900 * Cells(4, 22), 0)


Case 2000 To 4800
Cells(i, 13) = 1800 + Round(1800 * Cells(4, 22), 0)


Case Is >= 5400
Cells(i, 13) = 3600 + Round(3600 * Cells(4, 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




Private Sub individualsalary()


Dim aRange As Range
Dim totalpay As Double


  On Error Resume Next
  Set aRange = Application.InputBox(prompt:="Enter range", Type:=8)
  If aRange Is Nothing Then
    MsgBox "Operation Cancelled"
  Else
    aRange.Select
 
'Calculate D.A & H.R.A




    Cells(aRange.Row, 11) = Round((Cells(aRange.Row, 10) * Cells(4, 22)), 0)
    Cells(aRange.Row, 12) = Round((Cells(aRange.Row, 10) * Cells(5, 22)), 0)


'Calculate T.A
Select Case Sheets("Earnings").Cells(aRange.Row, 8)


Case 1800 To 1900
Cells(aRange.Row, 13) = 900 + Round(900 * Cells(4, 22), 0)


Case 2000 To 4800
Cells(aRange.Row, 13) = 1800 + Round(1800 * Cells(4, 22), 0)


Case Is >= 5400
Cells(aRange.Row, 13) = 3600 + Round(3600 * Cells(4, 22), 0)
End Select






totalpay = (Cells(aRange.Row, 10) + Cells(aRange.Row, 11) + Cells(aRange.Row, 12) + Cells(aRange.Row, 13) + Cells(aRange.Row, 14) + Cells(aRange.Row, 15))


Cells(aRange.Row, 16) = totalpay
End If


End Sub

Display More

calculate salary for specific record

Participate now!

Share