The problem with using the code below is that it leaves the formulae in the
cells. It can be modified to change the cell contents to value only, but
that is for another day. Unless you have thousands of rows, you could just
as easily enter a formula like =a1-b1 and copy it to whatever range you want.
But for sake of customer satisfaction, here is the code.


Sub sist()
ActiveCell.Formula = "=" & ActiveCell.Offset(0, -2). _
Address(RowAbsolute:=False) & "-" & ActiveCell.Offset(0, -1). _
Address(RowAbsolute:=False)
Set SourceRange = ActiveCell
Set FillRange = Range(ActiveCell.Address, Cells(Rows.Count, _
ActiveCell.Offset(0, -1).Column).End(xlUp).Offset(0, 1).Address)
SourceRange.AutoFill Destination:=FillRange, Type:=xlFillCopy
End Sub

Pick a cell on the right adjacent to two columns of numeric values and
select it. Then run the code.





"Exceller" wrote:

Thank you, Nicola.

What I'm looking for is code to place a formula in the cell to the right of
the two comparator cells. So, if cell C5 has a value of, say, "10" and D5
has a value of "20", then cell E5 will contain the difference of the two,
which would be "10". I would adjust the formula for the particular analysis.


The cell that contains the difference of the two comparator numbers would
begin wherever the active cell is: so, if the active cell is C12, then it
would place in that cell the difference of the values in cells A12 and B12.
Cell C12 would be the beginning of the range, and it would continue down as
far as there are values in the A and B columns, beginning at A12 and B12.

I hope this helps. Thanks.


"Nicola M" wrote:

I'm sorry but I don't understand what you need exactly. Is a mistake that you
indicated you want the formula in the same cell which contains data (C5). If
it is not this should tp involve VBA code works on Selection_Change event.
Please write some else example.
Nicola M.

"Exceller" wrote:

I've checked the forum for posts relating to this solution but didn't find
any. I'm looking for code that would enter a simple variance formula against
two columns for as many values as there are in the two columns.

So, if I have in cell B5 a value of 200 and in cell C5 a value of 100, then
the value in C5 would be 100 (simple subtraction of values in column C from
values in column B). The condition is that the formula works for any two
columns preceding the cell that is selected. So, if cell F10 is selected,
for example, then the formula will begin in F10, subtracting E10 from D10, on
down the F column for as many values as there are in columns D and E.

Is this do-able? Thanks.