Posts by Botner

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?

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.

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

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.

Thanks Dangelor for updating, this works very well for me!
Appreciate the help from everyone

I have tried with the codes provided above as stand alone codes. (Possibly they were partial codes i should implement in my own original?)

With the code from Dangelor i run into an error on line item 11: "Object variable or with block variable not set"

With the code from Davesexcel runs well but incase there is one ore more column headers in the "BDOG2N" which doesnt exist in the "G2N" sheet. I want to print/write the column header name somewhere so I can investigate these column(s) manually once makro has completed. This can be for example in a new work book or similar. Perhaps it is already doing it but i didnt find it? The code was so elegant i dont really understand much of it..!

Example workbook attached here

Hi, I have one sheet in my file named "G2N" which has column headers in a fixed order and i want to populate the rows under those columns with data from another sheet which i is called "BDOG2N".

The column headers are not in the same order in the different sheets and "BDOG2N" might have column headers that are not in the "G2N" sheet and vice versa.

I have tried to do this via VBA with the find function and running into a problem when I have a header in the "BDOG2N" which doesnt exist in the "G2N" sheet. If this happens, then i want to print/write the column header name somewhere so I can investigate this column manually once makro has completed.

Thank you

Sub G2N()


MF = ActiveWorkbook.Name
Workbooks(MF).Activate


BDOG2N = ActiveSheet.Name
Sheets(BDOG2N).Select


Cells(1, 1).Select
Range(Selection, Selection.End(xlDown)).Select
MyRows = Selection.Count


Cells(1, 1).Select
Range(Selection, Selection.End(xlToRight)).Select
MyCols = Selection.Count


For i = 1 To MyCols


Sheets(BDOG2N).Select
MyHeader = Cells(1, i)
Range(Cells(2, i), Cells(MyRows, i)).Select
Selection.Copy


Sheets("G2N").Select
Cells(1, 1).Select


Range("1:1").Find(what:=MyHeader, After:=ActiveCell, LookIn:=xlFormulas, _
lookat:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate




MyCurrentCol = ActiveCell.Column
Cells(2, MyCurrentCol).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False


Next i




End Sub