Challenge: Optimize VBA Max If Multiple Criteria

[pdevito3]

Hi everyone,

So I have a challenge for you all. I have some code that I've written that works perfectly, but is a fairly slow and inefficient at doing it's job. Wondering if anyone is up for a challenge and wants to come up with a more efficeint way of handling this.

Purpose:
The code looks at the table below and finds the max value for each "Content" that has a "Std" type. So for example, it looks at all of the A's that have a "Std" Type, finds the max, and puts that value into the inserted column M.

Here's the table:[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[TD]

G​

[/TD]
[TD]

H​

[/TD]
[TD]

I​

[/TD]
[TD]

J​

[/TD]
[TD]

K​

[/TD]
[TD]

L​

[/TD]
[TD]

M​

[/TD]
[/TR]
[TR]
[TD]

A​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Type​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Content​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Value​

[/TD]
[TD]

Inserted​

[/TD]
[/TR]
[TR]
[TD]

B​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Std​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

A​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

3​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

C​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Std​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

B​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

4​

[/TD]
[TD]

4​

[/TD]
[/TR]
[TR]
[TD]

D​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Std​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

C​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

5​

[/TD]
[TD]

5​

[/TD]
[/TR]
[TR]
[TD]

E​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

Std​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

A​

[/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD="align: center"][/TD]
[TD]

8​

[/TD]
[TD]

8​

[/TD]
[/TR]
</tbody>[/TABLE]


Here's the Code:

Sub FindMax()
'
    FinalMaxRow = Cells(Rows.Count, 1).End(xlUp).Row
    Columns("M:M").Select
    Selection.Insert
    Selection.NumberFormat = "General"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "Inserted"
    Range("M2").Select
    Selection.FormulaArray = _
        "=IF(RC[-1]=""N/A"",""N/A"",MAX(IF(C4=""Std"",IF(C8=RC[-5],C[-1]))))"
    Selection.AutoFill Destination:=Range("M2:M" & FinalMaxRow)
    'Selecting entire row should tell excel to "calculate" all rows
    Range("M2:M" & FinalMaxRow).Select
    Columns("M:M").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues
    Application.CutCopyMode = False
'
End Sub

Shoot with any questions. Enjoy

 

[pdevito3]

Here is the Non-R1C1 formula if it helps:

=IF(L3="N/A","N/A",MAX(IF($D:$D="Std",IF($H:$H=H3,L:L))))