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

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!

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

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

should do what you want.

--
Regards,
Tom Ogilvy



" wrote:

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!

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

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.

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

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.

--
Regards,
Tom Ogilvy


" wrote:

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.

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

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)

wrote in message
oups.com...
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.

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

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