PDA

View Full Version : Solved: Formula in whole column



blackie42
10-18-2007, 03:09 PM
I'm wanting to have a formula sit in a column ready for some other numbers being populated in other columns. So it auto calculates the result (without the bother of copying the formula down the column every time).

e.g

insert ?500 in column D & 0.5 in column F & the formula in column E would be =D(row no.)/F(row no.) and the result obviously 1000

Think I need to write this in a module within the sheet? But how to go about doing it? would it be a function - if so can someone help as I ain't done much with functions.

thanks a lot

Jon

Bob Phillips
10-18-2007, 03:15 PM
Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "D:D,F:F" '<== change to suit

On Error GoTo ws_exit
Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Cells(.Row, "E").FormulaR1C1 = "=RC4/RC6"
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.

lucas
10-18-2007, 03:16 PM
What do you mean by copy the formula down the column?
You do know that you can select the cell with the formula and hover over the lower right corner, left click and drag the formula down as far as you wish and it will adjust for rows...

blackie42
10-18-2007, 03:22 PM
thanks XLD - will give it a go - I'm sure it will work fine.

Thanks Lucas - although I did know about that (autofill options)- just wanted the formula 'locked' in a row if possible.

regards

Jon