VBA code required for automated escalations in excel

Subal

Here's a start. Put the code into a general module in excalations.xls and make sure that the workbook sec sample.xls is open.

There's a heap missing.
1) How do you want to check for the date? Have an input box that will ask for the relevant date? If you do this, then you have the option to check a range of dates rather that being restricted to only being able to run for a specific date based on the day of running.

2) What is the full list of workbooks being actioned? If you have the list, and they are in a particular place, then you could loop through the list, opening and closing as each is actioned.

3) I've used the sample as a reference for "Nothing found". If there is something else to check on please advise.

4) What about the other sheets in the workbook? Do they have any action or is it only sheets 1,2 and 3 that are being checked?

Please Login or Register  to view this content.

rylo

Hi Rylo

Thank you for your code. It’s working now (for 3 sheets).

I am not able to get your point number 1 - Input box.

Answer for your third question is 'Yes'. I have actions in all other sheets. The reason why I did not gave information about all the sheets at a one shot is not to confuse anyone...

So now I am back with another 3 sheets in the same workbook SEC SAMPLE.

Now I will explain next 4 sheets - sheet 4, sheet 5, sheet 6, and sheet 7

Sheet 4 – Column G refers to Abbreviation
Column M refers to Percent

If Abbreviation (column G) is anything other than XY, corresponding Percent (column M) should not be less than 105 and should not exceed 120

Similarly if Abbreviation (column G) contains XY, corresponding Percent (column M) should not be less than 102 and should not exceed 120

If there are any discrepancies (I mean if the above condition is not satisfied), I will copy the corresponding column C – Ref number and will search in the sheet 5, if this has a approved reason.

The approved reasons are below (this will be in the sheet 5)

Condition –
Any abbreviation greater than 100 but less than 102 (XY) or 105 (other than XY), below are approved reasons – DR, PL, Round, PR

If any abbreviation exceeds 120% then approved ones are Round, Two DC

Important thing to note is any Abbreviation that is less than 100 must be escalated in escalations tab (whatever may be the reason).

Sheet 6 should always contain ‘no data found’

Sheet 7 should always contain ‘No over No Under’

Thanks in advance

Subal

Hi

1) Date check. How do you know that the date in the file is the correct date? What do you want to compare it against? If you just do it against today's date, and you miss a day (say public holiday) then you will not get the code to action. My thought is to raise an input box and have the user input the date that the files should match.

2) As you have a heap of other sheets (missed that in your first post), how about you have a go at coding the others. From what I can see of your next stage, the basic premise has been covered in the response I've given you.

Have a go, and if you get stuck, then come back with questions. Helps you to learn that way.

rylo

Hi Rylo..

I am facing few issues while running the macro for the first 3 sheets..
The output i received in the escalations sheet is not correct. I have attached those files for your reference.

1) Even the correct things has got escalated in the escalations sheet.
For ex even the percentages greater than 102 are also escalated which is not correct.

2) In the escalations sheet, the number is in the format 0.99 instead of 99%

Kindly look into this ant let me know how to proceed on this.

Thanks

Subal

Subal

The format of your numbers between your first sample file and the file in your last post has changed.

1) Determine which file has the correct number format and adapt the code tests accordingly.

2) The number format can be changed along the lines of

Please Login or Register  to view this content.

Again, this will depend on the format of the data in your file, so determine what is the the correct data structure, then adapt the code to suit.


rylo

Rylo..

The final problem I am facing in the macro is that it's not checking all the percentages under one fund

For example

Under fund A the macro should check all the CL percentages.

But in this case macro is checking only for the first percentage under fund A.

For your reference I have coloured the items in red in the sec sample sheet which needs to be escalated.

Also i have attached the output i got this time.

Thanks for all your help in this.

Subal

Subal

Try this one

Please Login or Register  to view this content.

rylo