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:

A​

B​

C​

D​

E​

F​

G​

H​

I​

J​

K​

L​

M​

A​

Type​

Content​

Value​

Inserted​

B​

Std​

A​

3​

8​

C​

Std​

B​

4​

4​

D​

Std​

C​

5​

5​

E​

Std​

A​

8​

8​

<tbody>
</tbody>

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))))