How to insert "&" in a formula using VBA?

[michaellm]

Hi all,
My question is as followed.
Now the content of A2 cell is a formula like this:
=BLP(A1&" "&B1, "CLOSE_DATE")
Now I want to insert this formula using VBA.
The range is from C1 to C10.
So I code like this:
for i=1 to 10
Range("C" & i).Formula="=BLP(A" & i & "&"" ""&B" & i
&",""CLOSE_DATE"")"
next i
But it reports error.
What is the correct formula then?
Thanks!

 

[Guest]

Range("C1:C10") = "=BLP(A1&"" ""&B1, ""CLOSE_DATE"")"

should do what you want.

 

[michaellm]

Thanks Tom.
But I am sorry, maybe I did not put it clearly.
Actually, what I want is like this:
Range("C1")="=BLP(A1&"" ""&B1, ""CLOSE_DATE"")"
Range("C2")="=BLP(A2&"" ""&B2, ""CLOSE_DATE"")"
....
Range("C10")="=BLP(A10&"" ""&B10, ""CLOSE_DATE"")"

I want to put them in a loop.
How to do this then?
Thanks.

 

[Guest]

No, what you said was very clear and that is why I gave you the BEST answer.

What I gave you does EXACTLY that.

Doing it my way is >10 times faster than looping.

If you still want to do it the slow way, post back.

 

[Bob Phillips]

Tom's solution will load all the cells at once, adjusting the formula in
each one (well Excel does that bit).

It may not seem obvious, but try it and see.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[michaellm]

Oh, cool!!!!!
I just tried. It works exactly.
Thank you very much, Tom.