+ Reply to Thread
Results 1 to 54 of 54

Help on Tricky Periodic Analysis using UserForm!

  1. #1
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Help on Tricky Periodic Analysis using UserForm!

    Dearest Experts and Esteemed Forum Members,

    With all humbleness I request for help on being able to analyze my data between one period to another.

    Kindly refer to the attached spreadsheet with explanation and sample outcome in Tab 1 and sample data in Tab 2.

    Really hoping for someone to help me on this. I have cracked my brain enough and have spent considerable time doing it one by one, but to no great outcome.

    Thank you in hope of a positive response. I will be glad to provide any further clarification, if needed.

    Best regards,
    Brio
    Attached Files Attached Files
    Last edited by simplyxl; 06-22-2013 at 06:57 AM.

  2. #2
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Request your kind assistance please...

    Best regards,

    Brio
    Last edited by simplyxl; 06-22-2013 at 06:57 AM.

  3. #3
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dearest Experts and Esteemed Forum Members,

    May I request your consideration on the request.

    Thanks,
    Brio
    Last edited by simplyxl; 06-22-2013 at 06:57 AM.

  4. #4
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Sorry! Bumping it once again to request help. Please assist!

    Regards,
    Brio
    Last edited by simplyxl; 06-22-2013 at 06:58 AM.

  5. #5
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    You sent via PM a convoluted explanation of what you're looking for. The explanation makes no sense to me but it MAY to someone else. Please post it here in this Thread.

    Take your sample file and make the Periodic Counts agree with the Data and then explain fully how you got the numbers. I can't come up with your Counts. If one of us cannot come up with your Counts we can't write Code to do it...see attached jpg

    In addition, there is no UserForm in your workbook...

    6-22-2013 12-47-08 PM.jpg
    Last edited by jaslake; 06-22-2013 at 12:55 PM.
    John

    If you have issues with Code I've provided, I appreciate your feedback.

    In the event Code provided resolves your issue, please mark your Thread as SOLVED.

    If you're satisfied by any members response to your issue please use the star icon at the lower left of their post.

  6. #6
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    I agree it is a bit too tricky and my sincere apologies for the trouble.

    Thanks for looking into it though. Much appreciated!

    Thanks,
    Brio.

  7. #7
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Please explain what filters you used to get Platinum 106 and Platinum 21...Diamond 125 and Diamond 21.

  8. #8
    Forum Contributor
    Join Date
    11-11-2012
    Location
    Muscat, Oman
    MS-Off Ver
    Office 365
    Posts
    521

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi, Did you consider a pivot table. Attached please find a sample Pivot table for your ref. You can filter your commencement and closure dates and get the desired info.
    Best Regards/VKS
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Quote Originally Posted by jaslake View Post
    Please explain what filters you used to get Platinum 106 and Platinum 21...Diamond 125 and Diamond 21.
    Sure Sure John. Give me 5 minutes!

  10. #10
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Quote Originally Posted by jaslake View Post
    Please explain what filters you used to get Platinum 106 and Platinum 21...Diamond 125 and Diamond 21.
    I am of the understanding that you are looking at column B of my sample in post 1. The # are as follows:

    Gold 0
    Platinum 106
    Silver 0
    Bronze 20
    Diamond 125
    Others 12
    Premium 0


    Thanks a bunch sir! You made me breath a little better now.
    Attached Files Attached Files
    Last edited by simplyxl; 06-22-2013 at 01:44 PM.

  11. #11
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    The attached has the Code to populate Columns B and C with the requested counts. I haven't written the Code for Columns D, E, F and G. Do you wish to try it?
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Many many thanks! The first bit fills auto-magically. I was eagerly waiting

    The B and C are just perfect.

    Thank you and Thank you!

  13. #13
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    As I indicated
    I haven't written the Code for Columns D, E, F and G
    Do you want to have a go at it?

  14. #14
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Yes yes yes sir....I am immensely happy and would love to go for it. You are a gem!

    Thanks!

  15. #15
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Good for you. If you get stuck give me a holler.

  16. #16
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Oh....Out of excitement I misunderstood. I am only 1 month into VBA. So I am pretty sure I will not be able to accomplish it at this stage. I am sorry for the disconnect.

  17. #17
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    The Code in the attached will deal with Columns B through E. You'll need to explain clearer what you want in Columns F and G.
    Attached Files Attached Files

  18. #18
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Marvelous. Please see the explanation in the attached. I have intentionally used less data for explanation purpose.

    Please let me know if you think anything is still unclear.

    I love it and am loving it!
    Attached Files Attached Files

  19. #19
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Regarding this
    Also in the Column B to E the results are not taking into account the selected date. So If choose May 31. Then it is not counting for May 31 but only till May 30. What and where do I need to change it? Please suggest me.
    I don't understand...the counts in the file I attached exactly match the counts you manually placed in Columns B to E. So, what in the Filter have you changed? What should the Counts be?

    Let's stick with your original Data using March and June and not confuse the issue with different months. I need to see the Data and where it's coming from. Using different months just confuses the issue.

    Regarding Columns F and G...show me how you arrived at Platinum 45 and Platinum 5 and Bronze 10 and Bronze 3.

  20. #20
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    I think lets take it one by one to avoid any confusion.

    Attached is the sample of the highly manual way that I do it. Please check and let me know if I am clear or not.

    Thanks a lot.
    Attached Files Attached Files

  21. #21
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Do Columns B through E.work as expected?

  22. #22
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Quote Originally Posted by jaslake View Post
    Hi Brio

    Do Columns B through E.work as expected?
    Dear Sir,

    Sorry for the delay! I have been testing with my data and the script is working seamlessly. Apart from 1 input that will help fix columns B to E.

    The only thing that is not happening is that the script is not counting data for the selected date. Say if I select March 11, 2013 it Is only counting until March 10.

    Please see the attached with filters based on March 11, 2013 as the selection. The yellow rows are the ones which were on March 11. I want to include data from this date as well in my calculation.

    You are right on that part my actual numbers in the sample in post 1 are exactly the ones that the VBA script generates. This is because in the sample data there weren’t any line items with March 31 , 2013 as the commencement date. In other words, all were getting counted as per my sample within the evaluation period when I choose March 31, 2013.

    Please do let me know if you have any questions. Thanks a lot

    Best regards,
    Brio
    Attached Files Attached Files

  23. #23
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Change this line of Code from
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.
    Note the "<=" symbol...

  24. #24
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Thank you Sir. How silly of me i was putting the = sign in a different way, which is why it did not work.

    Now everything with B through E is just AWESOME!

    Only left with F and G....Please let me know if you need more clarity i.e. If i wasn't clear.

    Thanking you from the bottom of my heart. Doing it manually had caused me a brain stroke all this while.
    Last edited by simplyxl; 06-23-2013 at 10:35 AM.

  25. #25
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    I think I understand Column F...I still have issues with Column G...see attached jpg.

    6-23-2013 1-53-33 PM.jpg

  26. #26
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    In order to get this value I pick the most recent category for each of the entries that match in the comparison.

    So if Buyer 1 was both in March and June and In June he had multiple memberships say with the following dates:

    June 20 - Gold
    June 25 - Platinum

    Then I count it only under the unique LATEST category which will be Platinum in this case, and ignore Gold. I do this by looking through each one manually. And this is very much prune to errors becoz of lot of manual intervention.

    Please let me know if I don't make any sense.
    Last edited by simplyxl; 06-23-2013 at 02:47 PM.

  27. #27
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear Sir,

    I hope I haven't caused any confusion from my last post.

    The solution thusfar has proved to be the best I have ever seen in my small-time experience. You are a savior in the true sense.

    I am astonished and at the same time overjoyed after seeing all the results. It has helped me caught various errors from my manual efforts in the past. I am running the available part with my data. Thanks a billion to you and the Forum as a whole for letting me be a part of it.

    Regards,
    Brio

  28. #28
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    You ever watch the Movie Princess Bride? Great Movie...in my personal opinion a Classic. Well, in that Movie there's a line "He's only mostly dead". In your Thread I have Column F "mostly solved"...I'll be able to fix the rest of "mostly" in the "tomorrow's".

    With Column F I've duplicated with Code all that you do manually and have transfered the values to Column F of Periodic Counts.

    The remaining issue is Column G. You tell me
    In order to get this value I pick the most recent category for each of the entries that match in the comparison.

    So if Buyer 1 was both in March and June and In June he had multiple memberships say with the following dates:

    June 20 - Gold
    June 25 - Platinum

    Then I count it only under the unique LATEST category which will be Platinum in this case, and ignore Gold. I do this by looking through each one manually. And this is very much prune to errors becoz of lot of manual intervention.
    This manual process can be automated. You need to spell out, step by step, the EXACT process you do manually...EXACTLY...don't leave out words...tell me EVERY keystroke...

    Try the attached to see where we are...
    Attached Files Attached Files

  29. #29
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear Sir,

    Thank you for this. I will try the latest version attached and get back to you with the exact requirement step by step for Column G.

    Thanks,

    Brio

  30. #30
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear Sir,

    I have tried the entire code available and all seems to work simply perfectly in the testing I have done so far. I will continue it for some more time. I am sure nothing will come-up. You are just SO VERY GOOD!!!

    On revisiting the requirement in column G, it turns out to be simpler than I had actually presented. My mistake. I apologies for it. I mixed it up with the other phase of my analysis. Sign of danger I would say. I’ll have to hold on to my brain.

    Last Part - Revised Explanation: Column G + Data Extracts

    In Column G all I need is the distribution count per category for the ones highlighted in yellow and not the sum that was required for Column F (Only for the ones highlighted in yellow). The unique counts for category distribution are as follows: (example attachment in post 22) Bronze: 3; Diamond: 29; Platinum: 5 = 37 in total. Please refer to the attached Row 1 – I through L results updated in Green.

    Apart from this (and I am a little scared to request) – I really need your help on the last bit to get the data extracts (simple raw data) for each of the 3 date selections that I apply (C3, E3 and G3) each time. This is the data from Tab 2 used to update Columns B through G.

    Preferably, a separate workbook where:

    Tab 1 has row data with which B and C fields get updated
    Tab 2 has row data with which D and E fields get updated
    Tab 3 has row data (after VLOOKUP) with which data for F and G fields get updated

    I will be running this for 15000 entries and increasing daily spread over last 3-4 years and then ever 2nd day. I need to run this for every single month effective 2008 to date for historic analysis so getting the data extracts is immensely vital.

    Other than this it is AUTO-MAGICAL!. I will be more than glad to close this thread as “SOLVED” shortly.

    Many many kindest and humble regards.

    Brio
    Attached Files Attached Files
    Last edited by simplyxl; 06-24-2013 at 10:37 AM.

  31. #31
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Take a look at the attached...all Columns appear to be resolved.
    Attached Files Attached Files

  32. #32
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Unbelievable!!! I was so very much waiting for this sir...

    Absolutely phenomenal!

    I hope you already know how thankful I am. More than sure nothing will come-up. You are a true Guru.

    One question - Will this now also create the raw files for me as well?

    Thank you isn't enough. But I am truly thankful!

  33. #33
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Just ran it and it is creating the data in seprate tabs...WOW!

    Let me run it a bit more. I'll comeback sir.... Thank you!

  34. #34
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    I wish to express my most sincere gratitude and appreciation to you John. Thank you for providing me with such a brilliant solution!

    Apart from the efforts, the success of any help depends largely on the patience and understanding. Much much much appreciated. You are a superman (if I may say so) sir!

    Going forward and marking this as “SOLVED”

    May I additionally seek your permission to comeback in case I encounter any issues which I don’t think will come in my way. I’ll treasure this always.

    Will run it with utmost care on my original data!

    Thanks a billion!

  35. #35
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    You're welcome...glad I could help. Thanks for the Rep.

    If you have issues related to the Code we developed, by all means, contact me. If they are new issues please start a new Thread. One thing I would encourage you to do is test on a Month(s) that have all 7 Categories. I tried to anticipate this but did not test it.

  36. #36
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Thank you John. I will definately do that. Getting in all my data in one place.

    Let the auto-magic begin...

  37. #37
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear John,

    All is going really well! The tests and runs so far have proved immensely great.

    A little thing that is posing as a speed braker is the creation of the raw data files in separate tabs. What this make me do is copy the tabs in another workbook before each run.

    I was hoping to get something added so that the raw data files get created as separate xl books for each run. I need to run this nth times. Will this be easy to incorporate?

    Thank you,
    Brio

  38. #38
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    I'll look at this.
    What's the reason for saving all these test files? You could simply recreate them. What would you Name these Files?

  39. #39
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear John,

    Sorry but what I meant was that...Now since I'll be running this on my real data. I need the files with data for each analysis that I present.

    The file name should ideally have the selection dates so a file for March and June should be like this assuming I chose 31 as the date in March and 25 in June:

    March 31, 2013 Vs June 25, 2013

    Similarly, October 30, 2012 Vs January 31, 2013 and likewise...

    Thank you!
    Last edited by simplyxl; 06-25-2013 at 03:31 PM.

  40. #40
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Do you need the Pivot Tables or just the RESULTS of the Pivot Tables?

  41. #41
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Pivot tables are very helpful. So exactly the way it is now.

    Thank you.
    Brio

  42. #42
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    The Code in the attached has been modified for this
    the raw data files get created as separate xl books for each run
    Attached Files Attached Files

  43. #43
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Pure brilliance!

    I'll run it right now!

    Once again a big thank you.

  44. #44
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Dear John,

    This has been a real help. I finished my last quarterly analysis with flying colors. All thanks to you for this very accurate and brilliant VBA.

    I had 2 questions and wanted to seek your due permission prior to posting them. You've been extremely helpful, and did not by any chance want to do anything that may not be appreciated.

    Please let me know if I can take the liberty of asking those.

    Thanks,
    Brio

  45. #45
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Ask away...if I feel they're the subject of a new Thread I'll tell you.

  46. #46
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Thank you so very much!

    Question 1: Is there any other place that the range needs to be changed? I looked thoroughly, but could not find.

    I have few additional types of categories. So what I did was increased the range at 2 places:

    Module 1:
    Please Login or Register  to view this content.
    Module 3:
    Please Login or Register  to view this content.
    Question 2: What change will the script need in case I also want to run the calculations on specific months as a whole rather than specific dates, as it currently is? I am not aware how easy or hard it would be.

    I am referring to the script I am using that you provided in Post 42. It is magical!


    Thanks a ton!

    Other than this hope all is well at your end sir!

  47. #47
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    Looking at the Code it appears these are the only two lines of Code that need be changed to add additional Categories. What happens when you try it?

  48. #48
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Got it, thanks! It looks to work fine. Question 1 solved.

    Could you please shed some light on my:

    Question 2: What change will the script need in case I also want to run the calculations on specific months as a whole rather than specific dates, as it currently is? I am not aware how easy or hard it would be.

    Thanks a lot!

  49. #49
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    I've not looked at this for a while and I'm not certain I understand the question as I'm not familiar with what you're doing. But...what happens if you simply use the 1st day of the month and the last day of the month from the Calendar Control?

    If this is not as your describing please expand on your description.

  50. #50
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Completely understand sir! Absolutely appreciate your assistance...

    So when I select the specific dates say first of each month, it counts the live accounts on that particular date.

    However, what I want to do is get the complete count for that month and not just that date in particular which I select from the Calendar control. In other words, I would like to get a count of all active accounts between say January 1, 2013 - January 31, 2013. Is there a way to select the month through calendar control or some other way to get the monthly counts in columns B, C, D, E, F and G columns than counts for specific dates....?

    Please let me know if this still makes no sense...

    Thanks
    Last edited by simplyxl; 08-11-2013 at 01:02 PM.

  51. #51
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    I'd suppose there is a way
    Is there a way to select the month through calendar control or some other way to get the monthly counts in columns B, C, D, E, F and G columns
    At the moment nothing comes to mind except that it would be a major rewrite of the Code and require User Input as to which "View" the User is interested in "seeing" (Total Month vs Date).

    I'd suggest this is an Issue for a New Thread...you may PM me with a link to the New Thread if you wish. I, or anyone else will need a much better understanding of what it is you wish to accomplish.

  52. #52
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Got it sir,

    I'll surely post a new thread with details and definitely PM you the link. In the meanwhile, please let me know if a magical trick comes to your mind that may not need a complete re-write. As everything in my head is the same (all logics I mean) except that the calculations needed are on all of the month basis as against a specific date.

    Thanks a lot.

  53. #53
    Forum Expert jaslake's Avatar
    Join Date
    02-21-2009
    Location
    Atwood Lake in Mid NE Ohio...look it up.
    MS-Off Ver
    Excel 2010 2019
    Posts
    12,749

    Re: Help on Tricky Periodic Analysis using UserForm!

    Hi Brio

    I understand this
    As everything in my head is the same (all logics I mean)
    You need to get that logic into MY head or another for us to help

  54. #54
    Registered User
    Join Date
    05-29-2013
    Location
    MIE
    MS-Off Ver
    Excel 2007
    Posts
    94

    Re: Help on Tricky Periodic Analysis using UserForm!

    Yes sir. I will aim to be absolutely clear and precise.

    Thanks a lot

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1