Getting the differences of 2 cells from 2 different table

[gem28]

Hi! I'm working on an excel file that has 2 tables. I need to get the difference of 2 cells, each coming from the two tables. Ex:

tables 1 has: table 2 has: result should be:

1 2 3 3 2 1 -2 0 2
4 5 6 6 5 4 -2 0 2
7 8 9 9 8 7 -2 0 2

I need to get the difference of the two cells ex., 1-3 = -2 where 1 is from table 1; 3 is from table 2. The rest of the cells should have the same formula.

I have this code (which I generated from the recorded macro):

Public Sub Computation()
Range("AC211&quot.Select
ActiveCell.FormulaR1C1 = "=R[-44]C-R[-22]C"
Range("AC212&quot.Select
ActiveWindow.SmallScroll Down:=12
Range("AC211&quot.Select
Selection.AutoFill Destination:=Range("AC211:AC230&quot,
Type:=xlFillDefault
Range("AC211:AC230&quot.Select
Selection.AutoFill Destination:=Range("AC211:AV230&quot,
Type:=xlFillDefault
Range("AC211:AV230&quot.Select
End Sub

... since it uses the autofill method, it does not update the result immediately. The Calculation Option of MS Excel is set to manual. How should I go about it?

 

[Loomah]

Hi
Not really sure what you're trying to achieve here as it would probably be as easy to enter the formula once and copy it manually. However.....

Using the code you have generated from the recorder all you actually need is this, assuming your destination range is fixed ("AC211:AV230&quot and you have the correct formula.

Public Sub Computation()
Range("AC211:AV230").FormulaR1C1 = "=R[-44]C-R[-22]C"
Calculate
End Sub

The addition of "Calculate" does what it suggests and forces Excel to recalculate. Assuming there is some reason why you can't change Excel's settings to automatic calculation. However I have not tested this with your specific situation so you may not even need the Calculate - try it first without.

;-)

If a man says something and there are no women there to hear him, is he still wrong?
The faqs ma'am, just the faqs. Get the best from these forums : faq222-2244

 

[ETID]

assuming each value or digit is in it's own column

If it's just row by row subtraction,...

assuming table 1 is on sheet1 and table 2 is on sheet 2
and the data is col A on each sheet
then ...

=sheet1!a2-sheet2!a2

 

[gem28]

Thanks so much guys! Really appreciate it.

You know Loomah, you're code has made me realize the mistake I made that made my shortcut code not work. However, the reserved word "Calculate", makes my program endlessly loop. I should find a way to break that. But just this morning, I have found a code that surprisingly simple:

Public Sub Computation()

With Worksheets("Sheet1&quot
.Range("AC211:AV230&quot.FormulaR1C1 = "=R[-44]C-R[- 22]C"
End With

End Sub

I was so surprised to find out that it only takes these lines to solve my problem.

Thanks again guys! God bless!