none
How to insert a formula to a macro RRS feed

  • Question

  • Hello,

    Here is my probleme :

    In my Excel worksheet cell, I have a formula :

    =CONCATENATE("MM/", TEXT(A11,"0000"))

    I don't know how to insert this formula from my VBA code

    Range.("A11").Formula = ??

    Anyone knows how to do it?

    Thanks in advanced


    • Edited by Jagath1 Tuesday, June 7, 2016 7:34 AM
    Tuesday, June 7, 2016 7:33 AM

Answers

  • In my Excel worksheet cell, I have a formula :

    =CONCATENATE("MM/", TEXT(A11,"0000"))

    I don't know how to insert this formula from my VBA code

    Range.("A11").Formula = ??

    You can't write that formula into cell A11 because you create a circular reference if you do so.

    If you want to convert the value in cell A11 like the formula does, try that:
    Range("A11") = "MM/" & Format(Range("A11"), "0000")

    If you want to write the formula into an other cell as A11, come back and give me a sign.

    Andreas.

    • Marked as answer by Jagath1 Monday, June 13, 2016 7:35 AM
    Tuesday, June 7, 2016 10:17 AM
  • Thanks for your valuable answer. I made a mistake while posting my question.

    My equation is perfect and targeted cell should not be "A11" in the written VBA code  "Range.("A11").Formula = ??"

    However I found a solution by myself as follows.

    range("B1").Formula = "=CONCATENATE(""MM/"", TEXT(A1,""0000""))"

     

    Thanks again spending your valuable time for me.

     

    • Marked as answer by Jagath1 Monday, June 13, 2016 7:33 AM
    Thursday, June 9, 2016 2:31 PM

All replies

  • In my Excel worksheet cell, I have a formula :

    =CONCATENATE("MM/", TEXT(A11,"0000"))

    I don't know how to insert this formula from my VBA code

    Range.("A11").Formula = ??

    You can't write that formula into cell A11 because you create a circular reference if you do so.

    If you want to convert the value in cell A11 like the formula does, try that:
    Range("A11") = "MM/" & Format(Range("A11"), "0000")

    If you want to write the formula into an other cell as A11, come back and give me a sign.

    Andreas.

    • Marked as answer by Jagath1 Monday, June 13, 2016 7:35 AM
    Tuesday, June 7, 2016 10:17 AM
  • I don't understand the question. Are you trying to use the 'range.formula=' code to create the formula you list? If so, check out this: https://msdn.microsoft.com/en-us/library/office/ff838835.aspx

    If not, can you describe what you're trying to do?

    Tuesday, June 7, 2016 7:02 PM
  • Hi Jagath1,

    I think you are trying to assign a formula for the value residing in the cell A11.

    please check the suggestions given by the Andreas Killer and Dogubob.

    I hope you will get solution by their posts. if you still having a problem please let us know so that we can provide you further help regarding this issue.

    if you think that suggestion given by our community member worked for you then mark the suggestion as an Answer that solves your issue.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, June 8, 2016 12:00 AM
    Moderator
  • Thanks for your valuable answer. I made a mistake while posting my question.

    My equation is perfect and targeted cell should not be "A11" in the written VBA code  "Range.("A11").Formula = ??"

    However I found a solution by myself as follows.

    range("B1").Formula = "=CONCATENATE(""MM/"", TEXT(A1,""0000""))"

     

    Thanks again spending your valuable time for me.

     

    • Marked as answer by Jagath1 Monday, June 13, 2016 7:33 AM
    Thursday, June 9, 2016 2:31 PM
  • Thanks for your advise. I made a mistake in my question and finally I found the solution by myself.
    Thursday, June 9, 2016 2:36 PM
  • Thanks for your advise. I made a mistake in my question and finally I found the solution by myself.
    Thursday, June 9, 2016 2:37 PM
  • Hi Jagath1,

    its good to hear from you that you got the solution for your issue by yourself and thanks for updating the status of this thread.

    would you like to share your solution with our community so that if another community member have same issue like you can also get solution by your post.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 10, 2016 6:50 AM
    Moderator