Excel formula not calculating

[baba92]

Hi,
I tried to simplify my issue: I wrote in my cell C2 the following formula: '=SUM(1;2)

When I copy paste this formula in cell C4 I can read =SUM(1;2) in the cell but it does not calculate it. I need to click in the formula and just press enter for it to calculate.
I assume the quote is messing things up but when I control F for the quote, excel tells me there is no quote so I can't remove it.
I also did control F and replaced "=" by "=" which worked but it does not work on vba, I don't know why.

Basically I want my vba code to do this for many formulas. Copy pasting the formulas and then calculating it automatically.
I tried range("C4").formula=range("C2").formula but I received an error message ("Application-defined or obkect-defined error")

Do you have an idea?

 

[6StringJazzer]

Do not use the ' character. I'm not sure why you did. If anything in a cell starts with ' then the cell is treated as text. That is why you see the actual formula in the cell instead of a result. Excel will not display the ' in the cell but you can see it in the formula box and that is where you can delete it.

Your VBA looks OK. Please show the entire Sub you are trying to run. I recommend you use code tags--after pasting your code, select it and click the VBA button above.

 

[Joe4]

When you click on the cell with the formula, do you see that single-quote there?

If not, then your column is probably formatted as Text, in which case all you should need to do is change the format of the column to General (or something other than text) and re-enter the formula.

If you see the single quote there, you will need to remove it. You should be able to do that by higlighting that column, going to "Text to Columns" from the Data menu, and clicking "Finish", which should re-enter them all at once.

 

[baba92]

Do not use the ' character. I'm not sure why you did. If anything in a cell starts with ' then the cell is treated as text. That is why you see the actual formula in the cell instead of a result. Excel will not display the ' in the cell but you can see it in the formula box and that is where you can delete it.
View attachment 61421
Your VBA looks OK. Please show the entire Sub you are trying to run. I recommend you use code tags--after pasting your code, select it and click the VBA button above.

The thing is I don’t want excel to calculate the formula in C2. It’s a Bloomberg formula that is extracting a live value. I want to choose when it calculates or not. That’s why I put the ‘ to avoid the calculation.
But then I need to find an efficient way to remove it in C4

 

[Joe4]

The thing is I don’t want excel to calculate the formula in C2. It’s a Bloomberg formula that is extracting a live value. I want to choose when it calculates or not. That’s why I put the ‘ to avoid the calculation.
But then I need to find an efficient way to remove it in C4

Instead of messing around with that, why not set the Calculation Mode to your workbook to "Manual". Then nothing will calculate until you hit the F9 key.

Other than that, I would recommend using VBA to run code to get the value when you want it.

 

[baba92]

Instead of messing around with that, why not set the Calculation Mode to your workbook to "Manual". Then nothing will calculate until you hit the F9 key.

Other than that, I would recommend using VBA to run code to get the value when you want it.

Yes I wanted to do that but I feel that when I put the calc on manual, close the file, open other spreadsheets where I put calc=automatic and then reopen the first spreadsheet, calc are on automatic and not manual, i.e. excel set the manual/automatic calc not at a spreadsheet level, do you agree?

 

[baba92]

When you click on the cell with the formula, do you see that single-quote there?

If not, then your column is probably formatted as Text, in which case all you should need to do is change the format of the column to General (or something other than text) and re-enter the formula.

If you see the single quote there, you will need to remove it. You should be able to do that by higlighting that column, going to "Text to Columns" from the Data menu, and clicking "Finish", which should re-enter them all at once.

Yes the "Text to Columns" works but if I put it in my vba code and run the code, nothingis happening.

 

[Joe4]

See if this helps: How to Manually Calculate Only the Active Worksheet in Excel.

 

[baba92]

Do not use the ' character. I'm not sure why you did. If anything in a cell starts with ' then the cell is treated as text. That is why you see the actual formula in the cell instead of a result. Excel will not display the ' in the cell but you can see it in the formula box and that is where you can delete it.
View attachment 61421
Your VBA looks OK. Please show the entire Sub you are trying to run. I recommend you use code tags--after pasting your code, select it and click the VBA button above.

Sub Macro6()
Range("C4").Formula = Range("C2").Formula
End Sub

I receive this message "Application-defined or object-defined error"

 

[baba92]

See if this helps: How to Manually Calculate Only the Active Worksheet in Excel.

I just did the test but again when I reopen my spreadsheet, calculation options is back to automatic