Variable .FormulaArray

[helpexcel]

I'm using code to place this formula in the column A of a spreadsheet. Usually the code will update itself for each row, however this formula remains the same for every row. Any ideas why?

Range("A3:A" & LastRow).FormulaArray = "=Index(Data1,Match(1,(Data2=$G3)*(Data3=$T3)*(Data4=$L3)*(Data5=$K3)*(Data6<>Worksheet!$D$4)*(Data6<>Worksheet!$D$5),0))"

Thanks!

 

[Marcelo Branco]

To enter an array formula in a range is tricky.
You should either: loop; or insert in the first cell and fill down; or insert in the first cell and copy.
See
https://colinlegg.wordpress.com/2012/05/23/working-with-range-formulaarray-in-vba/

Hope this helps

M.

 

[helpexcel]

Thanks! Used .FillDown and it worked great.

 

[Marcelo Branco]

You're welcome. Glad to help.

M.

 

[helpexcel]

I'm trying this on another array but i'm getting an error. It works if I manually enter it.

Any ideas on why I'm getting this error for this? Run-time error '1004': Unable to set the FormulaArray of the Range class

Range("C3").FormulaArray = "=IFERROR(VLOOKUP(INDEX(Data1,MATCH(1,(Data2=$G3)*(Data3=$T3)*(Data4=$L3)*(Data5=$K3)*(Data6=Worksheet!$D$4),0)),DataField,1,0),"")"

 

[shg]

As a minimum,

[COLOR="#FF0000"]...DataFiel d[/COLOR],1,0),[COLOR="#00FF00"]""""[/COLOR])"

... but that red part looks sketchy.

 

[shg]

More generally, you might try assigning the formula to a string variable and printing it to the Immediate window. If it's correct, you can copy it from there and paste it into the cell. If it doesn't work, fix it. Then you can do

myCell.FormulaArray = myString

When you put it directly into the cell, you're flying blind.

 

[Marcelo Branco]

Thoughts
1.You should double the quotes
Range("C3").FormulaArray = "=IFERROR(VLOOKUP(INDEX(Data1,MATCH(1,(Data2=$G3)*(Data3=$T3)*(Data4=$L3)*(Data5=$K3)*(Data6=Worksheet!$D$4),0)),DataFiel d,1,0),"""")"
2. VLOOKUP(INDEX(...), DataField,1,0) returns the same value, if found, returned by INDEX (???) - it seems superfluous.
3. Be sure all the named ranges, Data1, Data2 etc, have the same size
4.Check
The result of INDEX(Data1, MATCH(1,....,0))
Is it present in DataField?

M.

 

[helpexcel]

Thanks!! the Red text is my error copying it over. I forgot about the extra "" when using .Formula

 

[Marcelo Branco]

Thanks!! the Red text is my error copying it over. I forgot about the extra "" when using .Formula

Glad it worked.

M.