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!

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!

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.

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.

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.

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