Solved

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

Posted on 2012-04-12
10
243 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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

862 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

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now