If you want to calculate fast and simply how much would somebody’s net salary be, then you might want to use the macro below.

Gross salary is deducted with following payments

Income tax: this is from employee’s tax card

Employee’s part for work pension: 7,15 % of gross salary in case the employee is 17-52 or 63-67 of age.

Employee’s part for unemployment insurance: 1,25 % of gross salary in case the employee is 17-64 of age.

This can be done easily by writing the formulas manually. However, if you do this repeatedly, sooner or later you will value a macro.

Fill the cells with yellow filling: gross salary and income tax percentage.

This model does not take into consideration taxable benefits like mobile phone or taxable bonuses on top of basic salary.

Sub z_payroll_basic_macro()

Application.ScreenUpdating = False

ActiveCell.FormulaR1C1 = "Gross"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Tax"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Pension"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "Unempl"
ActiveCell.Offset(-2, 2).Range("A1:A3").Select

Selection.NumberFormat = "0.00%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "7.15%"
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveCell.FormulaR1C1 = "1.25%"
ActiveCell.Offset(-2, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-1]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-2]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=ROUND(R[-3]C*RC[1], 2)"
ActiveCell.Offset(1, 0).Select


ActiveCell.FormulaR1C1 = "=R[-4]C-SUM(R[-3]C:R[-1]C)"
ActiveCell.Offset(0, -1).Range("A1").Select
ActiveCell.FormulaR1C1 = "netto"
ActiveCell.Offset(-4, 0).Range("A1").Select

    ActiveCell.Offset(0, 1).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveCell.Offset(1, 1).Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With

Application.ScreenUpdating = True
End Sub

I did not write this macro manually, but recorded two macros and modified them.

Screen updates have been turned off, that the screen does not flash, when the macro is executed. You can remove the code lines, if you want. In case you want the screen updates to be disabled in some other macros, you can copy the lines here and add them to another macro.

This blog has been published earlier but now updated according to the year 2020 percentages.

Values are downloaded to Excel. Dimension for the first row is A and second B and so on. Parameter for the first value is A, second is B and so on. When downloading the data, the parameter is missing and should be added manually.

The starting point.

The end result. This should be done.

This can be done in many ways.

Write manually A and B in A3 and in A4. Activate cells A3 and A4. Touch with cursor the square in low right hand corner in A4 and double click.

Sub Z_AB

ActiveCell.Value = “A”

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = “B”

ActiveCell.Offset(1, 0).Select

End sub

This macro is creating one pair of A and B. If you assign the macro to shortcut key and just press the key as many times as needed.

If you know that there are 20000 records of data and 10000 pairs of A and B should be added, you can use the macro below.

Sub Z_AB2

Dim c

For c = 1 to 10000

ActiveCell.Value = “A”

ActiveCell.Offset(1, 0).Select

ActiveCell.Value = “B”

ActiveCell.Offset(1, 0).Select

Next c

End sub

The macros are useful, if a zero value is presented as blank cell. The double clicking does not work.