putting an if statement into a cell

**MP3265** · 02-27-2013, 05:32 PM

Hi All,

I am struggling with a piece of code. I am trying to put an if statement into a cell as part of a larger macro. My active cell is Q2 and I am trying to place a formula into the active cell. If I were to type the if statement into excel I would want it to read below:

if(M2=1,"X",if(M2=2,"Y","Z"))

I am struggling with the syntax needed to input this formula.

Thanks in advance for the help!

**scott.s.fower** · 02-27-2013, 05:57 PM

activecell.formula = "=if(M2=1,""X"",if(M2=2,""Y"",""Z""))"

you need double quotes around the values you are looking for

**Break_Point** · 02-27-2013, 06:02 PM

Double quotes is the key.

Sub InsertIF()
ActiveSheet.Range("Q2").Value = "=if(M2=1,""X"",if(M2=2,""Y"",""Z""))"
End Sub

Alternatively you can use the FormulaR1C1 syntax:

ActiveCell.FormulaR1C1 = "=IF(RC[-4]=1,""X"",IF(RC[-4]=2,""Y"",""Z""))"

Or you can write the If statement into the VB code:

Sub IfVBA()
If ActiveSheet.Range("M2").Value = "1" Then
ActiveSheet.Range("Q2").Value = "X"
ElseIf ActiveSheet.Range("M2").Value = "2" Then
ActiveSheet.Range("Q2").Value = "Y"
Else
ActiveSheet.Range("Q2").Value = "Z"
End If
End Sub