Solved

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

Posted on 2012-04-12
10
260 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 

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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

752 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