FormulaArray Error

[dluhut]

Hello,

I've made a macro and below is the code that was given to me.

Please Login or Register  to view this content.

When I save this macro and try to re-run it, it's giving me an error

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

I tried searching in Google and says that, one possibility is that it could be too long.

However, I tried to shorten it, and still giving me the same error.

So what should I do in order for my cell to have this Formula Array written and then copied over?

[AB33]

Put the formula in to named range and then refer the range name in your formula.

[AB33]

http://www.excelforum.com/excel-prog...y-formula.html

OR

http://www.excelforum.com/excel-prog...-too-long.html

[dluhut]

When you say 'named range', are you defined name range? Or are you indicating to put it into a variable?

If you really refer to named range, I have to avoid it, because higher up doesn't understand complicated formula. Thus, I need to put it into as simple as possible where it'll show where it's taking the cell from and where it's doing the lookup from etc.

Tried below and still doesn't work

Please Login or Register  to view this content.

Put the formula in to named range and then refer the range name in your formula.

[Jacc]

If you attach a workbook (not pictures) with some data and the expected output it will be much easier to help you.

[AB33]

I have attached two links with two different approaches, one of them showing you how to split the array in to 255 limit.

[dluhut]

It's ok.

I gave up and I don't have time to play around with the macro.

Since I need to give this by end of day which is another 1 hour.

But in short, I'm trying to:
unlock certain sheets,
clear contents of specific cells,
put in an array formula where it's link to an external workbook in a network drive, <---this is where I'm stuck even though I was able to create the formula and use the macro to see how it was written, and still, when I try to run it, it's giving error
copy it and paste it across specified cells, and
locked back the sheets.

[AB33]

You do not need so, as Jacc has asked you to attach a sample. We will do the job for you.

[dluhut]

I'll upload the sample once I'm done with this. I need to learn this and to know how to do it going forward.

Thanks!