VBA FormulaArray

[jadown]

Hi,

I keep getting an error when I use this code. The formula is matching the ActiveWorksheet cells to a file name Current.xls and pulling the answer/data from the Current.xls. The formula works fine by just doing the array in the excel file but when I try to automate it using VBA code I get an error message saying "Unable to set FormulaArray property of the Range class".

Here is the code:
Range ("C62").FormulaArray = "=INDEX('[Current .XLS]qry_BFFinal'!$A$2:$G$786,(MATCH(B2&B62&(B9+1),'[Current.XLS]qry_BFFinal'!$A$2:$A$786&'[Current.XLS]qry_BFFinal'!$C$2:$C$786&'[Current.XLS]qry_BFFinal'!$F$2:$F$786,0)),7)"

[ragulduy]

I think it has to be done using R1C1 style references for the cells.

As an example (from the vba help documentation):

Please Login or Register  to view this content.

You might be able to get around it doing something like

Please Login or Register  to view this content.

[jadown]

yudlugar thanks for the help but that did not work.

[ragulduy]

You probably need to rewrite it as an r1c1 formula then.

[jadown]

Ok thanks. I think I will just use vlookups. I've never used FormulaArrays so the R1C1 maybe over my head. But thanks again for your help.