View Full Version : Solved: Is there a way around using a tonn of ascii references......

06-04-2010, 03:38 PM

I'm trying to use

Range("MyRange").Formula = "text here"

where I need to drop in

=SUBSTITUTE(B2 &" " & C2," ","+")

...as the text

With all of the quotation marks and ampersands, is there a better way than:
=SUBSTITUTE(B2 & Chr(38) & Chr(34) & Chr(32) & Chr(34) & Chr(32) & Chr(38) & Chr(32) & C2, & Chr(34) & Chr(32) & , & Chr(34).....etc etc etc

I know I can get double-quote the quotes, but this is giving me a headache. Is there some special brackets I can pop around this.

I've got a couple of these in ".Formula" places that are even worse, and it's getting overwhelming.

06-04-2010, 05:23 PM
I would just use

Range("MyRange").Formula = "=SUBSTITUTE(B2&"" ""&C2,"" "",""+"")"

but wouldn't this work just as well

Range("MyRange").Formula = "=B2&""+""&C2"

06-06-2010, 08:32 AM
That worked brilliantly. I was complicating that way too much. Thank you.