VBA for Payroll workbook - Print text explaining variances in salary

Botner · Jun 3rd 2021

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.

**royUK** · Jun 3rd 2021

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.

**KjBox** · Jun 4th 2021

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!

Botner · Jun 4th 2021

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!!

**KjBox** · Jun 4th 2021

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

**KjBox** · Jun 4th 2021

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)

Code

                                Case Is = 28, 29, 30, 31
                                
                                ' Ignore Bonus if any previous Bonus has a value
                                    If y(i, 9) = "" Then
                                        If x(ii, iii) > 0 Then
                                            If InStr(y(i, 9), "+Bonus") = 0 Then
                                                y(i, 9) = "+Bonus"
                                            End If
                                        Else
                                            If InStr(y(i, 9), "-Bonus") = 0 Then
                                                y(i, 9) = "-Bonus"
                                            End If
                                        End If
                                    Else
                                        If x(ii, iii) > 0 Then
                                            y(i, 9) = y(i, 9) & " " & "+Bonus"
                                        Else
                                            y(i, 9) = y(i, 9) & " " & "-Bonus"
                                        End If
                                    End If

Display More

to

Code

                                Case Is = 29
                                    If y(i, 9) = "" Then
                                        If x(ii, iii) > 0 Then
                                            y(i, 9) = "+PerfBonus"
                                        Else
                                            y(i, 9) = "-PerfBonus"
                                        End If
                                    Else
                                        If x(ii, iii) > 0 Then
                                            y(i, 9) = y(i, 9) & " " & "+PerfBonus"
                                        Else
                                            y(i, 9) = y(i, 9) & " " & "-PerfBonus"
                                        End If
                                    End If
                                Case Is = 30
                                    If y(i, 9) = "" Then
                                        If x(ii, iii) > 0 Then
                                            y(i, 9) = "+TargetBonus"
                                        Else
                                            y(i, 9) = "-TargetBonus"
                                        End If
                                    Else
                                        If x(ii, iii) > 0 Then
                                            y(i, 9) = y(i, 9) & " " & "+TargetBonus"
                                        Else
                                            y(i, 9) = y(i, 9) & " " & "-TargetBonus"
                                        End If
                                    End If

Display More

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

Hope that helps.

**KjBox** · Jun 4th 2021

Let me know if you get stuck!

Botner · Jun 4th 2021

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.

Botner · Jul 8th 2021

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?

VBA for Payroll workbook - Print text explaining variances in salary

Participate now!

Share

Tags