Auto Fill in Formula Per New Line

[Jason P]

I have an formula that I would like to fill downwards on the entering
of data in a column next. The formula I have is bellow;

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 5 Then
If Not IsEmpty(Target.Value) Then
Target.Offset(0, 1).FormulaR1C1 = "=RC[-1]*6"
End If
End If
End Sub

This is fine apart from when I change the formula to
"=IF(R$1=G2,"Yes","No")" a syntax error is shown.

I am probably going about this entirely the wrong way but any help
would be much appreaciated.

The end goal is to have the formula automaticaly entered every time a
new line is added to the sheet

Thanks

Jason

 

[Tom Ogilvy]

"=IF(R$1=G2,""Yes"",""No"")"

You have to put in 2 double quotes within a string to get one double quote
to appear in the string. When you only put in one double quote as your
formla string showed, excel interpreted the second one as the end of the
string.

Testing in the immediate window:

? "=IF(R$1=G2,""Yes"",""No"")"
=IF(R$1=G2,"Yes","No")