Solved

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

Posted on 2012-04-12
10
248 Views
Last Modified: 2012-04-12
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.
0
Comment
Question by:swjtx99
  • 6
  • 4
10 Comments
 
LVL 9

Accepted Solution

by:
macarrillo1 earned 500 total points
ID: 37839570
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.
0
 

Author Comment

by:swjtx99
ID: 37839647
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 "
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37839701
What is  (Date - [F2] + 4)?

when you calculated Total WIP you had:
Cells.SpecialCells(xlCellTypeLastCell).Row - 1
0
ScreenConnect 6.0 Free Trial

Discover new time-saving features in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 

Author Comment

by:swjtx99
ID: 37840217
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
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37840243
You should be able to use the round function to round to the number of digits you want.

round (your value, number of digits)
0
 

Author Comment

by:swjtx99
ID: 37840268
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 ..
0
 

Author Comment

by:swjtx99
ID: 37840277
Sorry, you've more than answered my original question so forgive me for not already awarding the points, I'll do that now.
0
 

Author Closing Comment

by:swjtx99
ID: 37840282
Nice to get expert help and learn something in the process.

I very much appreciate the assistance.

swjtx99
0
 

Author Comment

by:swjtx99
ID: 37840379
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!
0
 
LVL 9

Expert Comment

by:macarrillo1
ID: 37840381
Glad to be of help!
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

773 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question