ExcelBanter - copying formula

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - copying formula (https://www.excelbanter.com/excel-worksheet-functions/115983-copying-formula.html)

Hi

I use the following to test the last character in a cell.
=IF(RIGHT(E4,1)="A","Yes","No").

When I add more rows, I am always forgetting to copy the formula down. is
there anyway that i can set up the worksheet so that I dont have to remember

Thanks
A

Hi Alex

If you have Excel 2003, then use DataList to create your table.
Then as you add new rows, the formulae will automatically get added.

--
Regards

Roger Govier

"Alex" wrote in message
...
Hi

I use the following to test the last character in a cell.
=IF(RIGHT(E4,1)="A","Yes","No").

When I add more rows, I am always forgetting to copy the formula down.
is there anyway that i can set up the worksheet so that I dont have to
remember

Thanks
A

Alex,

you can achieve this using a VBA event procedure on the change event.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 And Target.Cells.Count = 1 Then
Target.Offset(0, 1).FormulaR1C1 = Cells(4, 6).FormulaR1C1
End If
End Sub

This procedure assumes that you are building your formula in column F:F
and that the first formula that you want to copy is in F4. It will fire
whenever you make a change in a cell in column E:E and it will copy the
formula of F4 into the cell right of the recently changed cell.

To install:
Right-click the sheet tab where you are building your data/formulas.
Choose Edit Code.
Paste the above code in the window that comes up.

HTH
Kostis Vezerides

Alex wrote:
Hi

I use the following to test the last character in a cell.
=IF(RIGHT(E4,1)="A","Yes","No").

When I add more rows, I am always forgetting to copy the formula down. is
there anyway that i can set up the worksheet so that I dont have to remember

Thanks
A

Thanks Both

"Alex" wrote in message
...
Hi

I use the following to test the last character in a cell.
=IF(RIGHT(E4,1)="A","Yes","No").

When I add more rows, I am always forgetting to copy the formula down. is
there anyway that i can set up the worksheet so that I dont have to
remember

Thanks
A