VBA for Payroll workbook - Print text explaining variances in salary

  • Hi community. I am looking to simplify a manual process.

    Each month I compare net salary from previous month against current month and make a short comment per employee what has impacted their net salary.

    All is done within same workbook - Attaching a sample file which I will refer to.

    What I want the makro to do is:

    In Net pay sheet column I "Current month" I want to print text explaining the variations in current month salary.

    All relevant salary data explaining the variations is find in the "Payroll data" sheet in different columns.

    For example if employee 647608 has had an Overtime payment he will have the monetary value in payroll data sheet column Z or AA. If he has a value in any of these columns, I simply want to print '+OT in the net pay. If he had multiple different payments I want to combine a text. For example '+OT +On Call +Bonus.


    I'd also like to include the logic that columns which are related to payments give a prefixed "+" if value is positive and "-" if value is negative.

    For columns related to deductions I want to give a prefixed "-" if value is positive and "+" if value is negative.

    We have for example 2 overtime columns (Z and AA). If an employee has values in all 2 I want to avoid printing OT text 2 times if possible (+OT +OT)


    What I am asking community to help with is to build the basis of the Makro. The loops etc. to cycle through all the employees rows in net pay sheet and all the columns in the Payroll data sheet.

    Then I can add in text that needs to be printed based on the columns.


    Let me know if any questions.

  • Quote

    All relevant salary data explaining the variations is find in the "Payroll data" sheet in different columns

    That makes it more difficult.


    Looping through all that data will be slow without having to check multiple columns. I would think that if the reasons were in one column it would be much easier to use formulas.

  • Try this


    Your file is attached with code. The code uses Select Case with a whole bunch of If statements, I have grouped some Additions together (OT, Bonus & Award). You can adjust the Select Case to ungroup or create new groups if desired.


    The code uses arrays to loop through all data and check multiple columns and is fast.


    I tried to put the code here too, but it is too long for this site, the reply exceeded 10,000 characters!

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Holy #%&%¤"##¤ it runs in less than a sec...!!!!!! My vba understanding is very basic but I will look through this, probably google around and hopefully manage to group and ungroup with this select case stuff. My mind is just blown away of how you can cook something like this up.
    Cheers!!

  • You're welcome, I did put some comments in the code to help you understand it.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Basically the Select Case is selecting the current value of the variant iii (iii is part of the For Next loop and starts at 24 and goes up to 42, increasing by 1 for each iteration of the loop. The 24 to 42 represents the column numbers of your Additions and Deductions (Y to AP).


    To check if a "grouped" Addition already has a text value, for example +OT, the code uses the Instr function to check, if that returns a value greater than zero then +OT is already there so the second OT is ignored (as long as it is + and not -). A Check is then made for -OT.


    The same principle applies to Bonus and Award.


    So to ungroup Bonus from you would need to change (Note in the code I put Case Is = 28, 29, 30, 31

    That was an error should be Case Is = 29, 30, 31 (28 is REFERAL Bonus and has already been done)


    to

    Then do the same for Cases 31 (Plan Bonus) - replace "TargetBonus" with "PlanBonus"


    Hope that helps.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Let me know if you get stuck!

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Thanks a lot KjBox. I really appreciate all the explanations/comments you added to the code and further explanations above. I will let you know incase I get stuck.

  • Hi KjBox - I have been working on the macro and understand quite a bit now. Have managed to adjust it to further needs and implement it on 3 of the monthly payrolls I am responsible for. However there is one small issue I hope you can help with. After running the makro, formulas I have in column C, G and H disappears. The formulas are included in the original workbook I uploaded. They seem to be somewhat pasted as values after the makro is run. I have tried walking through the makro step by step but I can't figure out which code is doing it. Is it something you could take a look at?