Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 79 of 79

Thread: Creating a single Table with different formulas Excel VBA

  1. #61
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    BTW, I just made the two changes you identified, and it ran fine for me.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  2. #62
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    They added two columns: Age of buyer and Annual Income (not sure why)

  3. #63
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Yeah, I noticed after posting that you said column J.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #64
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    It's all good now. Thank you so much
    They no longer added the columns

  5. #65
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    As I said Jazz, when I added the columns and made the changes you suggested, it worked fine.

    I have an update that makes changes like that more manageable, I added the Enums I mentioned, and also configured it so that you can change where the layout starts (the row number). The business should drive the IT, niot the other way around.

    Do you want that version ?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  6. #66
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hmmm it's okay

    Quick question, though. I am trying to use your way of coding in this particular question.
    This time I am trying to create a table showing whether they are P or F instead of the numbers.
    The formula I am using is

    =LOOKUP(2,1/(Monthly!A:A=Table!B9)/(Monthly!D:D=Table!C10)/(Monthly!J:J=Table!G8),(Monthly!L:L))
    This works if I type it manually in a cell.

    And my attempt at translating it is:

    Const FORMULA_SUB_CATEGORY As String = _
        "=LOOKUP(2,1/(<period>!C1=<level>)/(<period>!C4=Table!C3)/(<period>!C10=Table!R8C),(<period>!C12)"
    But I am getting an error here:

    .Cells(targetrow, "T").Resize(1, 2).FormulaR1C1 = Replace(Replace(FORMULA_SUB_CATEGORY, "<period>", "Monthly"), "<level>", """" & category & """")
    It says application defined or object defined error. What am I doing wrong?

  7. #67
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Hi, okay so I solved it. I just forgot the other ) at the end
    But it's super slow like it's taking over 5 minutes for that table alone -_-

  8. #68
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Try using the version I posted in #48, as I said it was much quicker.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  9. #69
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Yes that's what I am using but it's so slow.. Here's my new sample workbook. I made adjustments to it based on what they requested to change.

    Maybe you could change the other table..
    Attached Files Attached Files

  10. #70
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    I ran it five times, and these were the timings

    Elapsed time: 7.84765625
    Elapsed time: 4.46484375
    Elapsed time: 2.8515625
    Elapsed time: 2.64453125
    Elapsed time: 2.93359375

    Even 7.8 seconds, whilst slower than I was getting originally, is not unacceptable IMO, 3 secs certainly isn't.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  11. #71
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Uhmm my raw data actually has 7620 rows.. and 15 columns
    But it's okay now we used INDEX MATCH instead using the helper columns created during runtime.

  12. #72
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    wrong thread.

  13. #73
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Another question, though. How do I change the formula for Category rows (monthly and weekly) if for example, I filter the owner to a specific owner and then I filter the group?
    Like in the picture below: I filtered the owner Bob and then I filtered just group 2 under group column. the resulting number for category row for monthly and weekly should show either 100%, 50% or 0% only since there are only 2 sub categories under group 2

    a.jpg
    Attached Files Attached Files

  14. #74
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    I don't see the problem.
    If you use VBA instead of Excel formulae the results appear in less than 1 msec.
    Attached Files Attached Files

  15. #75
    VBAX Tutor
    Joined
    Jan 2020
    Posts
    204
    Location
    Quote Originally Posted by snb View Post
    I don't see the problem.
    If you use VBA instead of Excel formulae the results appear in less than 1 msec.
    Yes it's just that the dates change based on what month you'll choose on G1

    Anyway I was told to no longer do post 73

  16. #76
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    That is just so simple to adapt.

  17. #77
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    @ snb,

    the problem is that this not just a Report designed by a management committee, this is a Data Base to be used as a Report designed by a management committee and the OP must hack the Data Base directly for each Report, rather than using the Data Base as a source of Data for any Reports.

    Further confounding the issue is that the Data Base Fields contain two Types of data, either Percent or Int/Long. AFAIK even the same Named Record could use different Types on different Dates
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  18. #78
    Knowledge Base Approver VBAX Wizard
    Joined
    Apr 2012
    Posts
    5,635
    @Sam

    Can you show me where in my file the results differ from the desired results ?

  19. #79
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    ?
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

Tags for this Thread

Posting Permissions

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