+ Reply to Thread
Results 1 to 6 of 6

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

  1. #1

    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!


  2. #2
    Tom Ogilvy
    Guest

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

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

    should do what you want.

    --
    Regards,
    Tom Ogilvy



    "[email protected]" 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!
    >
    >


  3. #3

    Re: 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.


  4. #4
    Tom Ogilvy
    Guest

    Re: 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


    "[email protected]" 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.
    >
    >


  5. #5
    Bob Phillips
    Guest

    Re: 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)

    <[email protected]> wrote in message
    news:[email protected]...
    > 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.
    >




  6. #6

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

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


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1