Home » MS OfficeRSS

How to insert IF function using VBA

I have this IF-function i would like to create in VBA and paste into a cell of an excel sheet: =IF(C7="Innkommende";S7+T8;0)

I have this little test procedure:

Private Sub CommandButton1_Click()
    MsgBox ("=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)")
    Cells(4, 22).Value = "IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)" 'Writes the text to a cell
    Cells(2, 22).Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
    Range("V2").Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
End Sub

1st line with msgbox works ok

2nd line works ok, I get a text into a cell. I go to this cell and manually write the equal sign = in front, and the formula behaves as expected.

The next 2 lines do not work at all. I get run time error '1004' Application-defined or object-defined error. All cells used by the formula have stable data, text in cell C7 and figures in cells T7 and S8. What is wrong here?

Regards from Tore

 

 

3 Answers Found

 

Answer 1

Excel's VBA is USA centric.  You'll want to use comma as the list separator in
your formula:

Option Explicit
Private Sub CommandButton1_Click()
    Dim myFormula As String   
    myFormula = "=IF(C7=""Innkommende"",S7+T8,0)"   
    Me.Cells(4, 22).Formula = myFormula   
End Sub

Tore572 wrote:


I have this IF-function i would like to create in VBA and paste into a cell of an excel sheet: =IF(C7="Innkommende";S7+T8;0)

I have this little test procedure:

Private Sub CommandButton1_Click()
    MsgBox ("=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)")
    Cells(4, 22).Value = "IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)" 'Writes the text to a cell
    Cells(2, 22).Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
    Range("V2").Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
End Sub

1st line with msgbox works ok

2nd line works ok, I get a text into a cell. I go to this cell and manually write the equal sign = in front, and the formula behaves as expected.

The next 2 lines do not work at all. I get run time error '1004' Application-defined or object-defined error. All cells used by the formula have stable data, text in cell C7 and figures in cells T7 and S8. What is wrong here?

Regards from Tore



--
Tore

--

Dave Peterson

 

Answer 2

You could read about .formulalocal in VBA's help, too.

Dave Peterson wrote:


Excel's VBA is USA centric.  You'll want to use comma as the list separator in
your formula:

Option Explicit
Private Sub CommandButton1_Click()
    Dim myFormula As String
    myFormula = "=IF(C7=""Innkommende"",S7+T8,0)"
    Me.Cells(4, 22).Formula = myFormula
End Sub

Tore572 wrote:


I have this IF-function i would like to create in VBA and paste into a cell of an excel sheet: =IF(C7="Innkommende";S7+T8;0)

I have this little test procedure:

Private Sub CommandButton1_Click()
    MsgBox ("=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)")
    Cells(4, 22).Value = "IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)" 'Writes the text to a cell
    Cells(2, 22).Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
    Range("V2").Formula = "=IF(C7=" & """" & "Innkommende" & """" & ";S7+T8;0)"
End Sub

1st line with msgbox works ok

2nd line works ok, I get a text into a cell. I go to this cell and manually write the equal sign = in front, and the formula behaves as expected.

The next 2 lines do not work at all. I get run time error '1004' Application-defined or object-defined error. All cells used by the formula have stable data, text in cell C7 and figures in cells T7 and S8. What is wrong here?

Regards from Tore



--
Tore

--

Dave Peterson

--

Dave Peterson

 

Answer 3

Thanks. It worked perfectly for me. I wrote the commas in vba and they were translated to semicolons in the formula, and the formula was inserted without any errors. You helped me a lot.

Regards from Tore

 
 
 

<< Previous      Next >>


Microsoft   |   Windows   |   Visual Studio   |   Follow us on Twitter