Answered by:
How to insert a formula to a macro
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
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

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

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/enus/library/office/ff838835.aspx
If not, can you describe what you're trying to do?

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


Thanks for your advise. I made a mistake in my question and finally I found the solution by myself.
 Proposed as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Friday, June 10, 2016 6:50 AM
 Marked as answer by Deepak Saradkumar PanchalMicrosoft contingent staff, Moderator Friday, June 10, 2016 6:50 AM
 Unmarked as answer by Jagath1 Monday, June 13, 2016 7:31 AM

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