Solved: Subtracting Numbers with Sheets 1/2 data

[Anomandaris]

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]

[vba]

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
[/vba]

[Anomandaris]

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]

Yeah, range is correct.

This might be quicker

[vba]

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

.Formula = "=Sheet1!C17-Sheet2!C17
.Value = .Value
End With
[/vba]

[Anomandaris]

thanks this looks good, i'll try it out tomorrow and let u know

[GTO]

...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:

[vba]
Worksheets("Sheet3").Range("C17:BN475").Value = Evaluate("=Sheet1!C17:BN475 - Sheet2!C17:BN475")
[/vba]
or:
[vba]
Worksheets("Sheet3").Range("C17:BN475").FormulaArray = "=Sheet1!C17:BN475 - Sheet2!C17:BN475"
Worksheets("Sheet3").Range("C17:BN475").Value = Sheet3.Range("C17:BN475").Value
[/vba]

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

Hope that helps,

Mark

[Anomandaris]

thanks xld, and GTO

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