Formula in Range

[Exceller]

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.

 

[Bernard Liengme]

Sub tryme()
x = Selection.Offset(0, -1)
y = Selection.Offset(0, -2)
Selection.Value = x - y
End Sub


or do you want a formula?
best wishes

 

[Nicola M]

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]

It works perfectly. But is there a way to get it to go down the column, in
as many cells as there are cell values in X and Y? Thanks.

 

[Exceller]

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.

 

[JLGWhiz]

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]

Thanks, JLG. I work in Essbase all day and update dozens of templates per
day. The problem is that when I refresh a template Essbase wipes out the
formulae in my variance columns. So, I'm constantly having to recreate them,
and my comparator columns could run 2,000 rows. Do that 30 times a day and
you're ready to jump out of a window. Your code will save me a lot of time
(and frustration). It works perfectly. But I tried to insert code from a
"copy/ paste special/ values" macro I recorded, but it hoses it up. How
could I convert the formula column to values?

Thanks for your valuable help.

 

[JLGWhiz]

I think that if you add a line just before End Sub like:

FillRange = FillRange.Value

It should zap the formulae and leave just the values.