PDA

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



DanOfEarth
06-04-2010, 03:38 PM
Hi,

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.

xld
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"

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