Payroll Using Vlookup VBA

Payroll program using VBA and Vlookup
Create a user-form in the Visual Basic Editor with the same fields as in the Employee Information and Payroll Calculator worksheets in Excel. Now using a command button transfer the appropriate data from the filled user-form into the relevant worksheets. Using Vlookup then create a stub from the poulated worksheets. The complete code is given below:
Private Sub CommandButton2_Click()
TextBox1.Text = “”
TextBox2.Text = “”
TextBox3.Text = “”
TextBox4.Text = “”
TextBox5.Text = “”
TextBox6.Text = “”
TextBox7.Text = “”
TextBox8.Text = “”
TextBox9.Text = “”
TextBox11.Text = “”
TextBox12.Text = “”
TextBox14.Text = “”
TextBox16.Text = “”
TextBox19.Text = “”
End Sub
Private Sub CommandButton3_Click()
End ‘You can also use Unload Me
End Sub
Private Sub CommandButton5_Click()
Dim erow As Long
Dim ws As Worksheet
Set ws = Worksheets(“EmployeeInformation”)
ws.Select
ws.Activate
erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
Cells(erow, 3) = TextBox3.Text
Cells(erow, 4) = TextBox4.Text
Cells(erow, 5) = TextBox5.Text
Cells(erow, 6) = TextBox6.Text
Cells(erow, 6).Select
Selection.NumberFormat = “0.00%”
Cells(erow, 6) = Cells(erow, 6) / 100
Cells(erow, 7) = TextBox7.Text
Cells(erow, 7).Select
Selection.NumberFormat = “0.00%”
Cells(erow, 7) = Cells(erow, 7) / 100
Cells(erow, 8) = TextBox8.Text
Cells(erow, 8).Select
Selection.NumberFormat = “0.00%”
Cells(erow, 8) = Cells(erow, 8) / 100
Cells(erow, 9) = TextBox9.Text
Cells(erow, 9).Select
Selection.NumberFormat = “0.00%”
Cells(erow, 9) = Cells(erow, 9) / 100
Cells(erow, 10) = Val(TextBox6.Text) + Val(TextBox7.Text) + Val(TextBox8.Text) + Val(TextBox9.Text)
Cells(erow, 10).Select
Selection.NumberFormat = “0.00%”
Cells(erow, 10) = Cells(erow, 10) / 100
Cells(erow, 11) = TextBox11.Text
Cells(erow, 12) = TextBox12.Text
Cells(erow, 13) = Val(TextBox11.Text) + Val(TextBox12.Text)
End Sub
Private Sub CommandButton6_Click()
Dim erow As Long
Dim ws As Worksheet
Set ws = Worksheets(“PayrollCalculator”)
ws.Select
ws.Activate
erow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
Cells(erow, 1) = TextBox1.Text
Cells(erow, 2) = TextBox2.Text
Cells(erow, 3) = 40
Cells(erow, 4) = Val(TextBox14.Text) – 40
Cells(erow, 5) = Val(TextBox16.Text)
Cells(erow, 6) = 40 * Val(TextBox3.Text) + Val((TextBox14.Text) – 40) * Val(TextBox16.Text)
Cells(erow, 7) = (Val(TextBox6.Text) + Val(TextBox7.Text) + Val(TextBox8.Text) + Val(TextBox9.Text)) * ((40 * Val(TextBox3.Text) + Val((TextBox14.Text) – 40) * Val(TextBox16.Text)) / 100) + Val(TextBox11.Text) + Val(TextBox12.Text)
Cells(erow, 8) = TextBox19.Text
Cells(erow, 9) = (40 * Val(TextBox3.Text) + Val((TextBox14.Text) – 40) * Val(TextBox16.Text)) – (Val(TextBox6.Text) + Val(TextBox7.Text) + Val(TextBox8.Text) + Val(TextBox9.Text)) * ((40 * Val(TextBox3.Text) + Val((TextBox14.Text) – 40) * Val(TextBox16.Text)) / 100) – Val(TextBox11.Text) – Val(TextBox12.Text) –
TextBox19.Text
End Sub

View the training video:


Further reading:
Payroll

Leave a Reply

Your email address will not be published. Required fields are marked *