Solved: I can?t copy formula as text from one sheet to another.

[jiura]

Hello to all!
I have such problem. I write a program on excel and VBA. There is a blank cell on sheet 1. On sheet 2 I keep formula, witch I copy to the cell on sheet 1, when user clicks his button.
But I don?t want formula on sheet 2 be calculating. Because of this I put 1 space symbol before sign equal(? =?). My formula is looking as ? =If(A1<>0;1;2)?.You see? There is a space symvol before equal letter. And this formula doesn?t calculate on sheet 2. When user click button I want to execute my macros. My code is:
??????????????????
Sheets("01").Cells(4, 4).Value = Trim(Sheets("02").Cells(9, 5).Value)

Where ?Sheets("02").Cells(9, 5)? is a cell witch contain function with space. And this macros must delete this space, copy function to sheet 01 and calculate it there. But It doesn?t work. Excel Shows error: ?1004 Application defined or object defined error?. But Why? If I do this thing with keyboard: copy this function as text and then deleting space with keyboard ? all works! But If I do such thing with macros It doesn?t work!

[Simon Lloyd]

I don't understand why you need to store the formula as text? If it is a formula you change often you can store it in the cell as a formula and then copy the formula over to sheet 1 something like this:

[VBA]
Sub Copy_Formula()
Sheets("Sheet2").Range("A1").Copy
Sheets("Sheet1").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteFormulas
End Sub

[/VBA]

[jiura]

No! This doesn't approach.
I Explan:I need to keep this formula as text, becouse my Excel File is very big, and it's recalculate too long,I want to do this more quicky. Becouse of this I keep some formulas as text!

[lucas]

Why not just put the formula in with vba:
[VBA]Sheets("01").Cells(4, 4).Formula = "=A1+B1"[/VBA]

[crazyfray]

No! This doesn't approach.
I Explan:I need to keep this formula as text, becouse my Excel File is very big, and it's recalculate too long,I want to do this more quicky. Becouse of this I keep some formulas as text!

Not quite sure why you want to do this, as it might be better to change the calculation options (tools, options, calculation - change to manual, and only recalculate when you want to!). Or in VBA:

[vba]
'to go in workbook
Private Sub Workbook_Open()

Dim wWorksheet As Worksheet
For Each wWorksheet In Worksheets
With wWorksheet
.EnableCalculation = False
End With
Next wWorksheet
Application.OnKey "{F9}", "Calculatatator"
End Sub

[/vba]

And this in a new module
[vba]
Private Sub Calculatatator()
MsgBox ("Calculated!")
For Each wWorksheet In Worksheets
With wWorksheet
.EnableCalculation = True
.EnableCalculation = False
End With
Next wWorksheet
MsgBox ("'")
End Sub
[/vba]

Alternatively, if you are set on changing them to text, instead just add the ' character (apostrophe) at the start of the line in a cell.
'=1+2
will display as
=1+2
in the cell, and not
3

Make sense?

So all you need to do is something like

[vba]
this is pseudo code, untested, unlooked at

dim wk as worksheet
dim

for each wk in ActiveWorkbook
for each cell in wk
Cell.Value = text("'" & cell.value)
' (this is ", then ', then ")
next cell
next wk

'though I think that this will take forever and a day, as it's going to look at EVERY cell :P
[/vba]



Although ' is the character for commenting, since it's in quote marks it knows what you mean :P

[crazyfray]

Why not just put the formula in with vba:
[vba]Sheets("01").Cells(4, 4).Formula = "=A1+B1"[/vba]

Also, when I try this, it inputs the formula as a formula - I think the OP wants to have it as text.

[lucas]

I understood that he/she wanted it as text on sheet 2 so they could copy it to sheet one as an actual formula...

On sheet 2 I keep formula, witch I copy to the cell on sheet 1, when user clicks his button.
But I don’t want formula on sheet 2 be calculating.

also in post#1 notice his use of trim to try to get rid of the space so the formula will work as a formula in sheet 1.

[vba]Sheets("01").Cells(4, 4).Value = Trim(Sheets("02").Cells(9, 5).Value)
[/vba]
Where “Sheets("02").Cells(9, 5)” is a cell witch contain function with space. And this macros must delete this space, copy function to sheet 01 and calculate it there.

Maybe Jiura can clear up our questions. I'm not absolutly sure what the objective is.

[jiura]

I did what you tell me. But mistake anyway shows! I did such things:
Put Such formula in D4 cell
[VBA]'=IF(A1=0;2;4)[/VBA]
Then I try to copy this formula to D5 cell with macros:
[VBA]Sheets("Sheet1").Cells(4, 5).Value = Sheets("Sheet1").Cells(4, 4).Value[/VBA]

VBA shows me a mistake!

[lucas]

Hi jiura,
What I am saying is there is no need to copy from another sheet....
just run the macro to insert the formula when you need it. See attached.

[jiura]

Thank to all people that try to help me!

I solve this problem!
Now I answer: why my old macros was not working?

• When I Copy formula from one cell to another throw macros: It must be only an English name function. For Example if I copy function “=IF(A1=…….” - all works! But If I copy Russian (I’m from Russia) function “=ЕСЛИ(A1=…..” (equal to English If) it does not work!
• In Russian Excel version functions arguments divides with “;” but in English Version with “,”. It was the second problem!

The solve is: Copy in Macros English name functions and when Excel put then on sheet they’ll be automatically translate to Russian!