Transfer 350 character formula string from vba to Excel

[MikeTruscott]

Hi everyone!

Basically I build a string in vba which looks something like:

formula_string = "=R[18]C+R[36]C+R[54]C+........."

and then I paste it into a worksheet.

I've had no problems doing this before, but before now the string has only been around 20 characters long.

Now I've added more data to the model the string length has risen to over 300 characters in length! Now I get an error on the line:

Worksheets("Data__DAY").Cells(1,1).FormulaArray = formula_String

"Unable to set the FormulaArray property of the Range Class"

I'm assuming it's because the string is too long, but I can type a similar length formula in the actual sheet with no problems.

So is there anything I can do?

Any help would be greatly appreciated.

Cheers

Mike

 

[PHV]

You really want FormulaArray ?
Why not FormulaR1C1 ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[MikeTruscott]

Hi PHV,

Thanks for replying to my post.

I managed to solve the problem earlier by using .Formula instead of .FormulaArray

Is this what you meant? I'd not seen .Formula before, I got .FormulaArray from recording a macro.

I'm not sure what the differences are, but it works now and that's all that matters.

Thanks again,

Mike

 

[dhulbert]

mike there is a limit of 256 characters in an Excel formula.
One way you could deal it is to split your formula into 2 seperate cells 1 with the first half of the first half of the formaula and the second cell containing the second half of the formula and the first cell.