How to subtract many cell areas (between sheets)
Sub Tester1()
set rng1 = Sheet2.Range("A1:E30")
set rng2 = Sheet2.Range("A43:E65")
set rng1a = Sheet1.Range("A1:E30")
set rng2a = Sheet1.Range("A43:E65")
rng1.Copy
rng1a.PasteSpecial xlValues, xlSubtract
rng2.Copy
rng2a.PasteSpecial xlValues, xlSubstract
End Sub
--
Regards,
Tom Ogilvy
"Imbecill" wrote in message
...
Hi,
I'm sure a lot of you have better options to suggest! I have several cell
areas located on the same places in both sheets. To make it easy to
understand, say::
Sheet1.Range(A1:E30) and Sheet1.Range(A43:E65)
Sheet2.Range(A1:E30) and Sheet2.Range(A1:E65)
I need the Ranges in Sheet2 to be subtracted with the same ranges in
Sheet1.
Between them are values that contains formulas, text and are empty etc.
I started to code it using the PasteSpecial - Operation:=xlSubtract
approach, but I guess I can't copy - paste/subract all areas at once
because
excel can't make operations on several areas, right? And activate, select,
copy etc. every area and move between each sheet every time will be to
much.
Aren't there a "3D"- functions to use or something, I have heard of that.
Does it work on this? It had to be VBA, not cell functions!!
Any other ways? I don't want to loop through all rows, the action will
take
place every time the sheet opens (worksheet_activate ) and I got 200 rows.
of information to loop through and that will take to much time.
/Regards
|