Unable to set FormulaArray property error

[jolivanes]

From xld's array formula found here:
http://www.vbaexpress.com/forum/showthread.php?t=36181
When entering the array formula through code, the A30 and B30 don't change to the next (A31 and B31 etc) references.

Set TBFR = Range("B30", Range("B" & Rows.Count).End(xlUp)).Offset(, 20)
TBFR.FormulaArray = "=INDEX($J$2:$J$26,MATCH(1,($A$2:$A$26=A30)*($B$2:$B$26=B30),0))"

Changing the reference to RC notation I get a "Unable to set the FormulaArray property of the Range class" error.

Set TBFR = Range("B30", Range("B" & Rows.Count).End(xlUp)).Offset(, 20)
TBFR.FormulaArray = "=INDEX($J$2:$J$26,MATCH(1,($A$2:$A$26=RC[-21])*($B$2:$B$26=RC[-20]),0))"

How would I change this or do I need to enter the formula in one cell and then use autofill in the code?
Thank you in advance
John

[Bob Phillips]

[vba]

With Range("B30").Offset(, 20)
.FormulaArray = "=INDEX($J$2:$J$26,MATCH(1,($A$2:$A$26=A30)*($B$2:$B$26=B30),0))"
.AutoFill .Resize(Range("B" & Rows.Count).End(xlUp).Row - 29)
End With
[/vba]

[jolivanes]

Thanks again Bob.

You gave me something to digest also.
Why the space in .Autofill .Resize?
("B" & Rows.Count).End(xlUp).Row - 29). I need to get my head around this also.

But it works like a charm.
Thanks again and have a good weekend

John

[Bob Phillips]

The space in there is because the Resize is an argument of the Autofill function. Notice that With beforehand, it applies to both the .Autofill and the .Resize.

The Range("B" & Rows.Count).End(xlUp).Row finds the last poultaeds cell in column B, but this is the absolute row number, so you have to take 29 away to get the NUMBER of rows as we starting at row 30.

BTW, yours didn't work because you were effectively creating a array formula applying to the block of cells, not an array formula to each cell in the block.

[jolivanes]

poultaeds???
Is this an actual word Bob? Never heard of it and nothing in Google either.
Excuse my ignorance of the Queen's English.

All day I could not get it in my head about the -29. It finally dawned on me that you wrote rows where I had columns in my mind.
Serves me right for not grasping what is written I guess.
Thanks a lot again Bob.
John

[Bob Phillips]

poultaeds???
Is this an actual word Bob? Never heard of it and nothing in Google either.
Excuse my ignorance of the Queen's English.

Not that I know of, I meant populated, no idea where my fingers thought they were.

[jolivanes]

I tried to make another word out of it but never thought of populated which of course is the expression that makes sense.

Thanks Bob
John