FormulaArray Error 1004

[smartbuyer]

Hello geniuses,

Could anyone please help me figure out what I got wrong in this ActiveCell.FormulaArray code?
Been at it for an hour trying to find where the error comes from

Please Login or Register  to view this content.

Thanks in advance

[TMS]

Well, it looks like you're indexing a single cell, so I'm not sure how that's going to work ...

[smartbuyer]

Hi TMS,

The C6 refers to a column 6 in the Review Archive Sheet.
should it be written a different way?
I recorded this formula and got that

Thanks

[TMS]

As written, it looks (to me) is if it is trying to Index cell C6. If you want to index column 6, you'd use F:F.

Please Login or Register  to view this content.

[TMS]

Apologies, you're using R1C1 notation. Dohhhh...

Which column are you putting the formula in?

[smartbuyer]

Updated code with

Please Login or Register  to view this content.

still getting the error

[smartbuyer]

Apologies, you're using R1C1 notation. Dohhhh...

Which column are you putting the formula in?

Yes sorry I didn't make my self clearer. the formula is in column F of the "Request Status" sheet.
trying to get the data on Column F of "Review Archive" sheet via Index Match where 0(zero) or error is = to "" (blank)

[TMS]

I assume you have this formula working in a cell? What is the actual formula in the cell and what cell is it?

[TMS]

What error do you get and when do you get it?

[smartbuyer]

This is the actual formula in the cell

Please Login or Register  to view this content.

the error says
Run-time error ‘1004’ Unable to set the FormulaArray property of the Range Class

[Richard Buttrey]

Hi,

This will be easier to sort out if you upload the workbook that contains the formula already entered in the Excel sheet. We can then understand what it's doing. I'm presuming of course that the formula in the sheet does not give an error.

[smartbuyer]

Here's a sensitised version of the file

thanks you both!

[smartbuyer]

marking as solved as it seems there is a character limit of 255 on FormulaArray and the formula I am trying to add is about 350+ characters. Went a different route on this in terms of cells with 0(zero) results in formula.

as per https://support.microsoft.com/en-au/kb/213181


SYMPTOMS
When you try to create an array formula by using a Visual Basic for Applications macro, you may receive the following error message:
Run-time error '1004':
Unable to set the FormulaArray property of the Range class.

CAUSE
This problem occurs when you try to pass a formula that contains more than 255 characters, and you are using the FormulaArray property in Visual Basic for Applications.

[Richard Buttrey]

Hi,

You didn't show the formula in the worksheet so it's difficult to know which fields you are trying to match on. I'm somewhat puzzled since the data suggests the Order number is already a unique field which means that a trivial INDEX(MATCH()) formula will get what you want

If not and if several fields need to be matched add a helper column e.g. H2 =A2&B2&C2...etc that concatenates all the fields that determine what should be matched then the Index/match formula above can be used
e.g. in H2 use a formula

[TMS]

Create Dynamic Named Ranges for each of the columns on the Review Archive worksheet:

nrRA_A:

Please Login or Register  to view this content.

nrRA_B:

Please Login or Register  to view this content.

nrRA_C:

Please Login or Register  to view this content.

nrRA_D:

Please Login or Register  to view this content.

nrRA_E:

Please Login or Register  to view this content.

nrRA_F:

Please Login or Register  to view this content.

nrRA_G:

Please Login or Register  to view this content.

Then your Array Formula becomes:

Please Login or Register  to view this content.

And your code is then:

Please Login or Register  to view this content.

[TMS]

Note that, if you are expecting text/string values to be returned, rather than numeric data, your Array Formula could be just:

Please Login or Register  to view this content.

And the code would be this (putting it in column H for comparison):

Please Login or Register  to view this content.

[TMS]

Oh, and I should have said, using the Dynamic Named Ranges will not only make the formula shorter, but it will also improve calculation performance significantly over using whole column references.