Solved

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

Posted on 2012-04-12
10
257 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

680 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