VBA FormulaArray

**jadown** · 09-19-2013, 11:10 AM

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** · 09-19-2013, 11:22 AM

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

As an example (from the vba help documentation):

Worksheets("Sheet1").Range("E1:E3").FormulaArray = _
    "=Sum(R1C1:R3C3)"

You might be able to get around it doing something like

Range("C62")="=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)" 
Range("C62").formulaarray = Range("C62").formular1c1

**jadown** · 09-19-2013, 11:38 AM

yudlugar thanks for the help but that did not work.

**ragulduy** · 09-19-2013, 11:41 AM

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

**jadown** · 09-19-2013, 11:52 AM

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.

VBA FormulaArray

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

VBA FormulaArray

Re: VBA FormulaArray

Re: VBA FormulaArray

Re: VBA FormulaArray

Re: VBA FormulaArray

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] FormulaArray

FormulaArray

DDE and FormulaArray problem

Using FormulaArray in VBA

[SOLVED] Copy FormulaArray

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions