Writing a formula in VBA

[s7yzrs]

hello everybody,

I am trying to write this formula in VBA

Range("G50") ="=IF(DATI!J2="X",(ATA)+0.09/24,"N/A")"

but it doesn't work.

Can somebody please tell me what I am doing wrong?

 

[Logit]

Sub pasteFormula()
    Sheet1.Range("G50").Value = "=IF(DATI!J2=""X"",(ATA)+0.09/24,""N/A"")"
End Sub

You will need to edit the Sheet Name as required.

Using your formula as an example : "=IF(DATI!J2=""X"",(ATA)+0.09/24,""N/A"")" ... everything between the first QUOTE symbol
and the last QUOTE symbol, if it is to be displayed in the cell inside of quote symbols, will need to have double quote symbols.

Excel interprets the double quote symbols as an indication whatever is inside the double quotes will be displayed in the cell
as wrapped inside single quote symbols. Hope that made sense ?

Also, because you are entering the formula inside a cell, Excel needs to know that it will be a value .. hence the VALUE after
Sheet1.Range("G50").Value


Hope all that is understandable.

 

[vletm]

s7yzrs
Do this belong to same Your challenge with below Your 'previous' thread?

Help with time calculations and formulas

Hello everybody, I have a worksheet where some data is inserted via a userform. Textbox1, textbox2, textbox4 and textbox5 all have a time (format HH:MM). what I would like to achieve is this: when I click on the commandbutton of the userform the data is inserted in the chosen cells. At this...

chandoo.org

 

[s7yzrs]

Sub pasteFormula()
    Sheet1.Range("G50").Value = "=IF(DATI!J2=""X"",(ATA)+0.09/24,""N/A"")"
End Sub

You will need to edit the Sheet Name as required.

Using your formula as an example : "=IF(DATI!J2=""X"",(ATA)+0.09/24,""N/A"")" ... everything between the first QUOTE symbol
and the last QUOTE symbol, if it is to be displayed in the cell inside of quote symbols, will need to have double quote symbols.

Excel interprets the double quote symbols as an indication whatever is inside the double quotes will be displayed in the cell
as wrapped inside single quote symbols. Hope that made sense ?

Also, because you are entering the formula inside a cell, Excel needs to know that it will be a value .. hence the VALUE after
Sheet1.Range("G50").Value


Hope all that is understandable.

Hello Logit, thank you very much. It works perfectly!!

 

[s7yzrs]

s7yzrs
Do this belong to same Your challenge with below Your 'previous' thread?

Help with time calculations and formulas

Hello everybody, I have a worksheet where some data is inserted via a userform. Textbox1, textbox2, textbox4 and textbox5 all have a time (format HH:MM). what I would like to achieve is this: when I click on the commandbutton of the userform the data is inserted in the chosen cells. At this...

chandoo.org

Yes, it does. I figured out that I do not have to get automatic calculations. If I just can have formulas inserted via VBA I will achieve the same. Namely no matter what my colleagues will do to the sheet, the formulas will appear again when they open the sheet next time

 

[vletm]

s7yzrs
As You wrote this belongs to same challenge (= thread) then You should write these and next questions to SAME thread.
for s7yzrs , seems that You skipped above line.
Now, You have still that the 1st part of this thread unsolved ...

 

[s7yzrs]

s7yzrs
As You wrote this belongs to same challenge (= thread) then You should write these and next questions to SAME thread.
Now, You have still that the 1st part of this thread unsolved ...

not just the 1st part is unsolved. I still would like to know if there is a way to achieve what I have achieved with Logit's solution but without involving formulas inside the sheet

 

[Marc L]

Hi,​

​

as for a formula the Value property is not the appropriate one,​

better is to use one of the Range 'formula' properties, to see first in VBA inner help …​

​

Another must see in VBA inner help : Evaluate !​

​

 

[Logit]

You are welcome.
Merry Christmas.