PDA

View Full Version : Solved: Subtracting Numbers with Sheets 1/2 data



Anomandaris
05-14-2009, 08:41 AM
Hi guys,

another one here, this one's relatively straightforward. i need a macro that will subtract values in a range and enter the solution in a new sheet.

So Sheet 1 has numbers(with decimals) in the range C17:BN475
Sheet 2 also has numbers in that same range
I need to do - Sheet1 minus Sheet2 and have the answer pasted on Sheet3 in the same range("C17:BN475")

thanks

Bob Phillips
05-14-2009, 08:50 AM
For Each cell In Worksheets("Sheet1").Range("C17:BN475")

Worksheets("Sheet3").Range(cell.address).Value = _
cell.Value - Worksheets("Sheet2").Range(cell.address).Value
Next cell

Anomandaris
05-14-2009, 09:39 AM
Hey thanks xld,

It works, I added in 'Dim cell as range'
but its very slow, i guess it is for a large number of cells, but my excel froze and its still running...is there a faster way or did I choose the wrong 'Dim'

Bob Phillips
05-14-2009, 11:23 AM
Yeah, range is correct.

This might be quicker



With Worksheets("Sheet3").Range("C17:BN475")

.Formula = "=Sheet1!C17-Sheet2!C17
.Value = .Value
End With

Anomandaris
05-14-2009, 12:51 PM
thanks this looks good, i'll try it out tomorrow and let u know

GTO
05-14-2009, 05:02 PM
...but my excel froze and its still running...

Greetings to all,

Having the "advantage" of having a reaaallly slow laptop at home, I tested. Now here at work (fast PC) this would barely make a couple of tenths of a second difference, but if your machine if slower, you may wish to try:


Worksheets("Sheet3").Range("C17:BN475").Value = Evaluate("=Sheet1!C17:BN475 - Sheet2!C17:BN475")

or:

Worksheets("Sheet3").Range("C17:BN475").FormulaArray = "=Sheet1!C17:BN475 - Sheet2!C17:BN475"
Worksheets("Sheet3").Range("C17:BN475").Value = Sheet3.Range("C17:BN475").Value


While it is unlikely that I'll admit to the times I got at home (just makes me start crying:boohoo), the difference was notable.

Hope that helps,

Mark

Anomandaris
05-15-2009, 01:04 AM
thanks xld, and GTO

the codes work great, and damn fast, not even a blink