jolivanes
02-19-2011, 01:42 PM
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
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