swjtx99
asked on
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(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("F 1: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(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("F 1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10% Past Due = " & (Date - [F2] + 4) / Application.WorksheetFunct ion.CountI f(Range("F 1:F500"), "<" & (Date * 1)) & "&""Arial,Bold"" Percent "
Any help would be appreciated.
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(xlCellT
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(xlCellT
Any help would be appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
What is (Date - [F2] + 4)?
when you calculated Total WIP you had:
Cells.SpecialCells(xlCellT ypeLastCel l).Row - 1
when you calculated Total WIP you had:
Cells.SpecialCells(xlCellT
ASKER
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(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("F 1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10Percent Past Due = " & (Application.WorksheetFunc tion.Count If(Range(" F1:F500"), "<" & (Date * 1))) / (Cells.SpecialCells(xlCell TypeLastCe ll).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
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(xlCellT
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
You should be able to use the round function to round to the number of digits you want.
round (your value, number of digits)
round (your value, number of digits)
ASKER
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 ..
Assume I could just add this to the end of the line of code but still looking at examples to see ..
ASKER
Sorry, you've more than answered my original question so forgive me for not already awarding the points, I'll do that now.
ASKER
Nice to get expert help and learn something in the process.
I very much appreciate the assistance.
swjtx99
I very much appreciate the assistance.
swjtx99
ASKER
Woot!
After trying many syntax versions, I finally got the round function to work.
Code:
.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellT ypeLastCel l).Row - 1 & "&""Arial,Bold""&10" & Chr(10) & "Total Past Due = " & Application.WorksheetFunct ion.CountI f(Range("F 1:F500"), "<" & (Date * 1)) & Chr(10) & "&""Arial,Bold""&10Percent Past Due = " & Round(((Application.Worksh eetFunctio n.CountIf( Range("F1: F500"), "<" & (Date * 1))) / (Cells.SpecialCells(xlCell TypeLastCe ll).Row - 1) * 100), 2) & "&""Arial,Bold"" Percent "
Thanks again!
After trying many syntax versions, I finally got the round function to work.
Code:
.LeftHeader = "&""Arial,Bold""&10Total WIP = " & Cells.SpecialCells(xlCellT
Thanks again!
Glad to be of help!
ASKER
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(xlCellT