Macro that inserts info in left Header from sheet needs a tweak. (most of it works)

In the left header of a sheet, I currently have:

Total WIP = (count of rows on sheet)
Total Past Due = (count of dates in column F that are older than today's date)

Code:

.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1))

I would like to add % past due and thought I could just add a carriage return and then the part of the formula that gave me the total past due divided by the part of the formula that gives me the Total WIP....but it's not working.

So what I want in the left header is:

Total WIP = (count of rows on sheet)
Total Past Due = (count of dates in column F that are older than today's date)
% Past Due = (total past due/total WIP)

Code I've tried: (it returns the number 5 which is wrong, should be 9.52%

.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10% Past Due = " & (Date - [F2] + 4) / Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & "&""Arial,Bold"" Percent "

Any help would be appreciated.
swjtx99Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Michael CarrilloInformation Systems ManagerCommented:
Perhaps I am missing something:

You have:

& Chr(10) & "&""Arial,Bold""&10% Past Due = " & (Date - [F2] + 4) / Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & "&""Arial,Bold"" Percent "

which is Total WIP/Total Past Due.

But you said that you wanted Total Past Due/Total WIP.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
swjtx99Author Commented:
Nice catch.

What I want to end up with is % past due so from the sheet, there are 6 of 63 past due which is 9.52%

Ok I reversed it and it comes up as 0.2 so that's still not 9.52%......or .0952.

I added *100 and i'm getting 20. Any ideas?

Latest code:

   .LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10% Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) / (Date - [F2] + 4) * 100 & "&""Arial,Bold"" Percent "
Michael CarrilloInformation Systems ManagerCommented:
What is  (Date - [F2] + 4)?

when you calculated Total WIP you had:
Cells.SpecialCells(xlCellTypeLastCell).Row - 1
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

swjtx99Author Commented:
Wow. Another blunder on my part.

Ok, I fixed that and it works.......sort of. It's giving me the right number but I'm getting

Percent Past Due = 9.52380952380952

Code:

.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10Percent Past Due = " & (Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1))) / (Cells.SpecialCells(xlCellTypeLastCell).Row - 1) * 100 & "&""Arial,Bold"" Percent "


Would you happen to know a way i can trim this down to 2 digits of resolution? (9.52)

Thanks a lot for your help. Also appreciate your style of not just telling me the answer but giving me direction to figure it out as that is helpful for actually learning something.

swjtx99
Michael CarrilloInformation Systems ManagerCommented:
You should be able to use the round function to round to the number of digits you want.

round (your value, number of digits)
swjtx99Author Commented:
I was just googling and looking at some examples. Does "(your value...") have to be the same value all the time? Today it was 9.52380952380952. Tomorrow it might be 12.49394993....or 24.023049234....or anything.

Assume I could just add this to the end of the line of code but still looking at examples to see ..
swjtx99Author Commented:
Sorry, you've more than answered my original question so forgive me for not already awarding the points, I'll do that now.
swjtx99Author Commented:
Nice to get expert help and learn something in the process.

I very much appreciate the assistance.

swjtx99
swjtx99Author Commented:
Woot!

After trying many syntax versions, I finally got the round function to work.

Code:

.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellTypeLastCell).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10Percent Past Due = " & Round(((Application.WorksheetFunction.CountIf(Range("F1:F500"), "<" & (Date * 1))) / (Cells.SpecialCells(xlCellTypeLastCell).Row - 1) * 100), 2) & "&""Arial,Bold"" Percent "

Thanks again!
Michael CarrilloInformation Systems ManagerCommented:
Glad to be of help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.