Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill in Formula Per New Line
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Auto Fill in Formula Per New Line
"=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") -- Regards, Tom Ogilvy "Jason P" wrote in message om... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Auto Fill formula when inserting a new line | Excel Discussion (Misc queries) | |||
Possible to auto-fill in gaps between numbers on straight line bas | Excel Worksheet Functions | |||
Auto formula fill | Excel Discussion (Misc queries) | |||
Auto fill of formula | Excel Worksheet Functions | |||
Formula auto-fill help!! | Excel Worksheet Functions |