255 Characters

[gerald24]

Hi,

Anyone who knows how to bypass the 255 Characters rule in VBA? I have to put something that has a formula containing 1599 characters. Unfortunately, I don't know how to incorporate the formula in vba.

TIA

 

[JoeMo]

One way is to break up the formula into parts defined as strings, then insert the formula as a concatenation of the parts. What formula do you want to use? Is it an array formula?

 

[gerald24]

Hi Joe,

It is one with a lot of IF Formulas, but I believe not an array. can you provide an example please? I can not picture it.

 

[JoeMo]

Hi Joe,

It is one with a lot of IF Formulas, but I believe not an array. can you provide an example please? I can not picture it.

An array formula would be one that is confirmed using ctrl+shift+enter not just enter if you were entering it directly on a worksheet. When viewed in the formula bar it would be surrounded by curly brackets { and }.

 

[gerald24]

Hi Joe,

thanks for sharing that to me. Could you use the below formula to fix the codes?

=IF(B16=C16,"B Matches C",IF(B16=D16,"B Mathes D","Not matched"))


Thanks

 

[RoryA]

If it isn't an array formula, there is no 255 character limitation. For a simple formula like that, you just use something like this:

Activecell.formula = "=IF(B16=C16,""B Matches C"",IF(B16=D16,""B Mathes D"",""Not matched""))"

Note that you have to double up any quotes that are part of the formula string, as highlighted above.

 

[gerald24]

Thank you RoryA. works like magic