Function with variable number of conditions

  • forum.ozgrid.com/index.php?attachment/67660/


    Hi folks!
    I'd like to create a function that would take criteria from Lookup_ref sheet and return a category name on Data sheet, column H. Something like:



    Custom categories would be created from populated fields and would take into account the operator equals, contains .. right below the header.
    I thoght of making UDF worksheetfunction, where loop creates a string of nested IFs for each row combination but I'd probably hit the nesting limit. On the other hand,
    I am not sure how to create a VBA IF statement of variable lenght.

  • Re: Function with variable number of conditions


    I'm not sure I'd like to try and code that as a function - too many twists and turns... my opinion only, I'll stress.


    Without comments in the message, an alternative approach in the attached copy of your workbook - the code is commented to describe what it's doing. It would be easy enough to extend this to include comparisons like <, <=, >, >=, Not Equal and any other combinations you can think of. At the moment, it only works with String data, Numbers, Dates and Boolean can be added easily.


    One caveat - the workbook must be saved or else new (unsaved) data will not be found but it's just a matter of a quick click on save before running...

  • Re: Function with variable number of conditions


    Quote from cytop;761211

    I'm not sure I'd like to try and code that as a function - too many twists and turns... my opinion only, I'll stress.


    Without comments in the message, an alternative approach in the attached copy of your workbook - the code is commented to describe what it's doing. It would be easy enough to extend this to include comparisons like <, <=, >, >=, Not Equal and any other combinations you can think of. At the moment, it only works with String data, Numbers, Dates and Boolean can be added easily.


    One caveat - the workbook must be saved or else new (unsaved) data will not be found but it's just a matter of a quick click on save before running...


    Hi Cytop, some really impressive tip I must say. I will slighly modify the code and try it out a bit later, since I have no experience with ADODB on excel itself yet.
    But really appreciated. Thanks much !

  • Re: Function with variable number of conditions


    Quote from xladept;761212

    Hi Pulsar,


    How do you intend to invoke this function? (I think it should be a sub by the way):)


    Hi xladept, I think I'll have a sub that checks data table for column called category, then it creates the column or/and just populates it by the function or possibly an SQL query.

  • Re: Function with variable number of conditions


    If you want this as a function, you should specify all the arguments in the declaration statement.


  • Re: Function with variable number of conditions


    The logic fails:



    *the red will always pass the first test???

  • Re: Function with variable number of conditions


    Quote from mikerickson;761218

    If you want this as a function, you should specify all the arguments in the declaration statement.



    Hi mikerickson, unfortunately, the upper number of arguments can be more (or less) than 4 at every user. Haven't tried to pass a dynamic array yet. Thanks for your note.

  • Re: Function with variable number of conditions


    Quote from xladept;761219

    The logic fails:



    *the red will always pass the first test???


    Hi xladept, I think this line breaks the logic;



    Code
    For d = 3 To wd.Range("B" & Rows.count).End(xlUp).Row: CAT = [COLOR=#ff0000]wd.Cells(d, 8)[/COLOR]


    Sorry if my first post was not in plain English. The red categories on Data sheet were already hard-coded results. Initially they will be blank and after macro is executed, values from Lookup_ref sheet shall be returned there.
    But thanks for a solid analysis here. I will try to modify your code for variable number of columns on ref sheet. Have a nice day

  • Re: Function with variable number of conditions


    Quote from pulsar777;761234

    Hi mikerickson, unfortunately, the upper number of arguments can be more (or less) than 4 at every user. Haven't tried to pass a dynamic array yet. Thanks for your note.


    Have you looked at Optional arguments. One can have several optional arguments and specify their defaults



    or ParamArray arguments. You can pass up to 29 arguments


    To pass an array as an argument, you can use code like this

  • Re: Function with variable number of conditions


    You can arrange for a ParamArray to handle array arguments.


  • Re: Function with variable number of conditions


    This fixes the overwrite problem - nice code by the way:)


  • Re: Function with variable number of conditions


    Thanks xladept! And it was in front of my eyes all time!


    Anyways, I've been fascinated with ADO option that cytop offered. I tried to carry on that as an SQL newbie and got stuck at opening recordset, when the keyword "WHEN" from CASE - END is not recognized.
    A short recap:
    1. Macro should go through every row on data sheet.
    2. For that row, run a loop of every category combination on lookup_ref sheet
    3. Take the category which meets most of the criteria on ref sheet and paste that from Ref onto data sheet, col H.
    4. If more category rows meet the logic per particular data row (like row 4 = either MNO or STU) then take the last one (STU)
    > unfortunately, data cannot be organized different way.


    Therefore, can I ask you SQL geeks to help me out?
    Here's my code:


    forum.ozgrid.com/index.php?attachment/67705/

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!