Team, please let me start with a disclaimer that I don't have much VBA hands on Experience, but record excel macros sometimes to automate a few repetitive tasks or write small pieces of code to do simple tasks by reading through Google and forums like these.
My Requirement and things I have done till now are below for your reference. I request for help if you could, please.
Attached are two files [attachment]"Report_before.xls" [/attachment] & [attachment]"Report_After.xls"[/attachment] to show how the input and Output look like after I complete my Manual steps on the file.
1) I have the input file [attachment] "Report_Before.xls" [/attachment] that has multiple rows. This data is basically the day wise work hours of the associates. Each Employee has two rows against his EMPlId (Column A). The first Row ( Resource Category : REGWK in Column B) has the Total work hours of the employee on every day of the month. The Second Row ( Resource Category: REGOT in Column B) is empty to start with and needs to be populated.
2) For Every day of the month in Row 1 that has > 8 hours ( eg:10 hours), the row below for that associate should have difference of hours greater than 8 captured ( 10-8 = 2) - I am currently manually performing it on every second row using the if statementand for all days in the row. Basically these are the Overtime Hours above 8 hours worked by the associate on a particular dayFormula:Please Login or Register to view this content.
3) Once the Over time is calculated for all the EmpLIds, those extra hours need to be removed from the first row ( REGWK rows). I am currently doing this by filtering the Column B, selecting the Range and running a small macro to replace all hours greater than 8 hours by 8 hours.
Please Login or Register to view this content.
The Final Output looks like the [attachment] Report_After.xls [/attachment]
I would appreciate any help in automating the first part of this work, where I am manually doing the If Statement to calculate the Over Time.
Bookmarks