How to subtract many cell areas (between sheets)
 

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

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

How to subtract many cell areas (between sheets)
 

Thank you Tom,

Once again you've helped me. I'm greatful for your help.

/Regards


"Tom Ogilvy" skrev i meddelandet
...
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

Problem: How to subtract many cell areas (between sheets)
 

Tom,
I turned into a problem with the code. Since the cells subracted contains
formulas, will a formula be created every time a subtraction occurs, and
because the subtraction occurs when ws_activate event the number of formulas
in the cells accumulate.

How do I clear the content before I paste, so the cell product always will
contain just "cell2-cell1?" I have tried "Application.rng1a.ClearContent "
but it doesn't like the syntax. What is wrong and what can I do?

The question is open, if any else is skilled to answer.


/Regards?

"Tom Ogilvy" skrev i meddelandet
...
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

Problem: How to subtract many cell areas (between sheets)
 

rngA1.ClearContents

If sheet1 has formulas, then you are correct that the formula will progress
each time.

=formula
=(formula)-3
=((formula)-3)-6

for example.

But what else would you expect? How can you subtract a value from a value
produced by a formula without doing something like that? You would have to
be more explicit in what you want to happen in this situation. You could
make all the formulas hard coded numbers before doing the pastespecial, then
you would just get the net result (but you lose your formula).

--
Regards,
Tom Ogilvy





wrote in message ...
Tom,
I turned into a problem with the code. Since the cells subracted contains
formulas, will a formula be created every time a subtraction occurs, and
because the subtraction occurs when ws_activate event the number of
formulas
in the cells accumulate.

How do I clear the content before I paste, so the cell product always will
contain just "cell2-cell1?" I have tried "Application.rng1a.ClearContent "
but it doesn't like the syntax. What is wrong and what can I do?

The question is open, if any else is skilled to answer.


/Regards?

"Tom Ogilvy" skrev i meddelandet
...
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