Range with Name Manager

[trough]

In VBA, I have a multi column range on one sheet (DocMgr!$Q$8:$V$27) where, on another sheet, I need to find the max value in the second column if the first columns meets a certain condition. The way I found to do this is to write an array formula to a cell and then get the resulting value like this:

Sheets("Tables").Cells(1, 1).FormulaArray = "=MAX(IF(DocMgr!Q8:Q27=10,DocMgr!R8:R27))"

However, I would like to use the Name Manager to define the range instead of hard-coding it in VBA but it doesn't seem to work. In the Name Manager I have:

MgrTbl = DocMgr!$Q$8:$V$27

In VBA I did this but VBA does not lie it:

Sheets("Tables").Cells(1, 1).FormulaArray = "=MAX(IF(" & Range([MgrTbl]).Columns(1) & "=10," & Range([MgrTbl]).Columns(2) & "))"

Why won't this work? Is there a way to use the Name Manager variable I've created to accomplish what I'm trying to do?

 

[Scott Huish]

Range("MgrTbl")

 

[trough]

I am using Office 2016.

 

[trough]

Range("MgrTbl")

Thank you. I made the small change from [MgrTbl] to "MgrTbl" but I still get a Type Mismatch error.

 

[Alex Blakenburg]

How about:

Sheets("Tables").Cells(1, 1).Formula = "=MAXIFS(INDEX(MgrTbl,0,2),INDEX(MgrTbl,0,1),""10"")"