VBA- Array Formula with Dynamic Last Cell

[willwill88]

Hi,

I'm trying to make an array formula with vba where the logical test stops at the last row. I'm hoping this will speed up the macro instead of scanning a whole column C:C for example, which could have 2,000 - 600,000+ rows.

Sheet 1 has the data
Sheet 4 is where im putting the fomula

I was hoping this forumla would work but no dice. Any help would be appreciated.

Selection.FormulaArray = _
"=MAX(IF(C2=(Sheet1(C2:C & LastRow),(Sheet1.Range(U2:U & LastRow),""""))"

Sheets(1).Activate
LastCol = Cells(2, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, "B").End(xlUp).Row
Sheets(4).Activate
Range("AI2").Select

Code works but is slow
Selection.FormulaArray = _
"=MAX(IF(RC[-32]='Reservation Details By Date'!C[-32],'Reservation Details By Date'!C[-14],""""))"

'Error with this code
Selection.FormulaArray = _
"=MAX(IF(C2=(Sheet1(C2:C & LastRow),(Sheet1.Range(U2:U & LastRow),""""))"

'Error with this code
Selection.FormulaArray = _
"=MAX(IF(C[-32]=(Sheet1(C[-32] & LastRow),(Sheet1.Range(C[-14] & LastRow),""""))"

 

[Sal Paradise]

You could do the same with named ranges and then don't need VBA:

ColA is:
=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)

ColB is:
=OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B:$B)-1,1)

So here are the formulas:

Excel 2003
	A	B	C
1	ColA	ColB	Formula
2	B	8	10
3	C	6	10
4	B	4	10
5	E	4	9
6	A	5	9
7	E	8	9
8	B	8	10
9	D	1	10
10	E	1	9
11	E	3	9
12	D	9	10
13	E	7	9
14	A	8	9
15	B	4	10
16	B	5	10
17	A	3	9
18	B	1	10
19	B	9	10
20	D	5	10
21	B	10	10
22	D	10	10
23	B	9	10
24	E	9	9
25	A	8	9
26	A	9	9
27	E	4	9
28	C	2	10
29	D	6	10
30	C	10	10
31	C	7	10
Sheet2

Cell Formulas
Range	Formula
C2	{=MAX(IF(A2=ColA,ColB))}
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
ColA	=OFFSET(Sheet2!$A$1,1,0,COUNTA(Sheet2!$A:$A)-1,1)
ColB	=OFFSET(Sheet2!$B$1,1,0,COUNTA(Sheet2!$B:$B)-1,1)

No need for VBA.