Consulting

Results 1 to 4 of 4

Thread: range issue :(

  1. #1
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location

    range issue :(

    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

  2. #2
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    It would probably help if you posted the code that creates the address string and actually assigns the formula to the cell.
    Be as you wish to seem

  3. #3
    VBAX Contributor
    Joined
    Jul 2017
    Location
    Zurich
    Posts
    132
    Location
    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

  4. #4
    VBAX Master Aflatoon's Avatar
    Joined
    Sep 2009
    Location
    UK
    Posts
    1,720
    Location
    If you need a formula anywhere near that long, you've done something very wrong...
    Be as you wish to seem

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •