range issue :(

[nikki333]

Hi folks

I'm trying to put some intontiguous ranges (concatenated as string) into formulas via vba.

It seems tricky, excel uses every way to prevent me from achieving .)

Anyways, for the formulas, I gather range.addresses(0,0) from ranges, add the worksheet.name between "'" and add them to a string. (e.g. 'GF'!U8:U70;'Fo1'!U8:U80;'Fo2'!U8:U41;'Fo3'!U8:U56)

The strange thing is that excel will ignore the beginning "'" and remove it from the final formula in some cases and throw an error, and strangely for some ranges not .)

WTF, any ideas, cheers

[Aflatoon]

It would probably help if you posted the code that creates the address string and actually assigns the formula to the cell.

[nikki333]

Thank you very much,

For the ...address i found that the lenght limit is 255; to overcome this stupidity, i've found a solution (a function that returns the range as a string, no matter what the lenght is).

As for the actual issue:

Meanwhile i have been able to figure out where the problem lies...it's that excel doesn't accept formulas that are longer than 7-8k characters (somewhere between, i don't remember the exact figure).

But that's quite annoying. I've tried to put my complicated ranges into named ranges, but then there's the same length problem.

Always running into "Out of memory" error; apparently due to the length limit of the formula.

Damnit, Excel is always good for throwing surprises lol

[Aflatoon]

If you need a formula anywhere near that long, you've done something very wrong...