Run-time error '1004': Unable to set the FormulaArray property of the Range class

Hi everyone,

I was trying to use a code to set formula (array) for a range of cells as below, but this drove me mad as it failed to work:

Please Login or Register  to view this content.

Here, MaxDD is a UDF (tested and works properly).

Excel always says Run-time error '1004': Unable to set the FormulaArray property of the Range class.

So what is the problem?

""
should be """"
Stings should be in double quotes.
If the formula length is over 255 character, you will get another error.

For j = 1 To 100
Worksheets("Data").Cells(j + 1, "B").FormulaArray = "=MaxDD(TRANSPOSE(IF(ISNUMBER(1/RC[1]:RC[120]),RC[1]:RC[120],"""")))"
Next j

Thank you so much AB33. This helped me a lot. Finally got this solved .