Max If Multiple Conditions Dynamic Ranges VBA

[GiacomoSA]

Hi,

I am struggling with VBA syntax to implement a Max If over multiple conditions on dynamic ranges.

I have a list of products and I want to find the most expensive according to 2 or more criterias.

On top of it the list of products is dynamic.

What would the correct syntax be for having a macro that returns 46 if the user inputs as chosen criterias: Blue Shirt?

I managed to get some results with a dynamic range using formulas of this sort

Selection.FormulaArray = _
"=MAX(IF(Sheet1!R6C6:R96C6=""Blue"",IF(Sheet1!R6C5:R96C5=""Skirt"",Sheet1!R6C7:R96C7)))"

but I don't know how to convert the formula above into a dynamic formula with "Cells()" and "for loops".
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]PRODUCTS[/TD]
[TD]COLOUR[/TD]
[TD]PRICE[/TD]
[/TR]
[TR]
[TD]SKIRT[/TD]
[TD]BLUE[/TD]
[TD]50[/TD]
[/TR]
[TR]
[TD]PANTS[/TD]
[TD]BLUE[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]PANTS[/TD]
[TD]YELLOW[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]SKIRT[/TD]
[TD]YELLOW[/TD]
[TD]47[/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD]YELLOW[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD]BLUE[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD]BLUE[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD]RED[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]PANTS[/TD]
[TD]YELLOW[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]SKIRT[/TD]
[TD]RED[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]SHIRT[/TD]
[TD]YELLOW[/TD]
[TD]46[/TD]
[/TR]
[TR]
[TD]SKIRT[/TD]
[TD]RED[/TD]
[TD]67[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Paste this code in your Data Worksheet code module.
Close code module.
Assuming your data starts in "A1", then double click "A1", this will load 2 validation lists in "D1" & E1".
You should then be able to select you Clothing/Colour combinations from those 2 cells for the Max Price to show in "F1".

Private [COLOR="Navy"]Sub[/COLOR] Worksheet_BeforeDoubleClick(ByVal Target [COLOR="Navy"]As[/COLOR] Range, Cancel [COLOR="Navy"]As[/COLOR] Boolean)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]If[/COLOR] Target.Address(0, 0) = "A1" [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] Ac = 0 To 1
        [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, Ac)
            Dic(Dn.Value) = Dn.Value
        [COLOR="Navy"]Next[/COLOR]
[COLOR="Navy"]With[/COLOR] Range("D1").Offset(, Ac).Validation
    .Delete
    .Add Type:=xlValidateList, Formula1:=Join(Dic.keys, ",")
[COLOR="Navy"]End[/COLOR] With
Dic.RemoveAll
[COLOR="Navy"]Next[/COLOR] Ac
[COLOR="Navy"]End[/COLOR] If
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]
Private [COLOR="Navy"]Sub[/COLOR] Worksheet_Change(ByVal Target [COLOR="Navy"]As[/COLOR] Range)
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range, n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object, c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] nStr [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]String,[/COLOR] Ray() [COLOR="Navy"]As[/COLOR] Variant
Application.EnableEvents = False
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
  [COLOR="Navy"]If[/COLOR] Not Intersect(Target, Range("D1:E1")) [COLOR="Navy"]Is[/COLOR] Nothing [COLOR="Navy"]Then[/COLOR]
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Offset(, Ac)
           [COLOR="Navy"]If[/COLOR] Dn.Value = Range("D1").Value And Dn.Offset(, 1).Value = Range("E1").Value [COLOR="Navy"]Then[/COLOR]
               c = c + 1
               ReDim Preserve Ray(c)
              Ray(c) = Cells(Dn.Row, 3).Value
            [COLOR="Navy"]End[/COLOR] If
        [COLOR="Navy"]Next[/COLOR]
        [COLOR="Navy"]If[/COLOR] c > 0 [COLOR="Navy"]Then[/COLOR]
                [COLOR="Navy"]If[/COLOR] UBound(Ray) = 1 [COLOR="Navy"]Then[/COLOR]
                    Range("F1").Value = "Only Value = " & Ray(1)
                [COLOR="Navy"]Else[/COLOR]
                    Range("F1").Value = "Max Value = " & Application.Max(Ray)
                [COLOR="Navy"]End[/COLOR] If
            [COLOR="Navy"]Else[/COLOR]
                Range("F1").Value = "Not Found"
            [COLOR="Navy"]End[/COLOR] If
       [COLOR="Navy"]End[/COLOR] If
Application.EnableEvents = True
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[GiacomoSA]

Thanks Mick for your reply I'll try to do some work on your code since I am not really getting any data validation in D1 or E1.

 

[MickG]

Try this Example with the validation cells "D1 & E1" already loaded.
NB:- You should only need to double click "A1" once to load the Lists after that its just about selecting the values in those lists.
https://app.box.com/s/i8tnad0lhnabmq3lz15184k3vy3bmkwc

 

[GiacomoSA]

Perfect thanks it is all working. I had a problem with my EnableEvents It is perfect now.

 

[MickG]

You're welcome