range.formula: text string and cell reference

Hi,

I'm missing something no doubt very simple...

All I want to do is paste a formula to a cell (within an If statement - different formulas for different cases)

If typed directly into the cell it would take the form..

="ABC "&A1&" DEF"

so it would result in text string: some number from another cell: second text string

in VBA I've tried

Range("A11").Formula = "=ABC" & "&A1&" & " DEF"

but I just get a #NAME error.  Am I missing extra quotes or something?

Any help greatfully recieved!

Cheers

Ah, this can get tricky when the string you wish to specify contains quotes.

I usually do it in a couple steps:

1) Start with your original string:  ="ABC "&A1&" DEF"

2) Replace each quote with two sets of quotes:  =""ABC ""&A1&"" DEF""

3) Surround your text string with quotes to finish: "=""ABC ""&A1&"" DEF"""

Viola!

You need to 'double up' all the quotes, otherwise VBA interprets the single quotes as the end of the text string.

Rick

Rick, that's a nifty way to approach that. I might actually be able to make such a procedure work!

Thanks for the replies.

This approach works! Thanks

I now have another problem.... not sure if it should go here or in another thread...

You did right starting another thread - I think it helps keep things organised, and allows you to mark this one as closed/answered (as I see you already have). ;-)