Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Exceller" wrote in message ... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. "Bernard Liengme" wrote: 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 -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Exceller" wrote in message ... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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" wrote: 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Formula in Range
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. "Exceller" wrote: 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" wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I enter formula sum(range+range)*0.15 sumif(range=3) | Excel Discussion (Misc queries) | |||
conditional formula: sum a range if text present in another range | Excel Discussion (Misc queries) | |||
formula to sort a range so that it matches the exact rows of a column that is outside that range? | Excel Discussion (Misc queries) | |||
Macro to input formula in range based on another range | Excel Programming | |||
Range.Formula and Range question using Excel Automation | Excel Programming |