Add and Subtract ranges (with multiple cells) in vba

[Guest]

I'm trying to subtract one range from another, but instead of looping through
cell by cell, I think it should be possible to perform this opeation in only
a line or two.

Below is the code for what I'm trying to do. Starting_HC, Final_HC and
Turnover are all named ranges with 17R and 11C. Any ideas on what I can
change to make this work?

Sub Headcount()

Range("Final_HC").Value = Range("Starting_HC").Value -
Range("Turnover").Value

End Sub

 

[Rob Bovey]

I'm trying to subtract one range from another, but instead of looping
through
cell by cell, I think it should be possible to perform this opeation in
only
a line or two.

Below is the code for what I'm trying to do. Starting_HC, Final_HC and
Turnover are all named ranges with 17R and 11C. Any ideas on what I can
change to make this work?

Sub Headcount()
Range("Final_HC").Value = Range("Starting_HC").Value -
Range("Turnover").Value
End Sub

Hi Jon,

Here's one way to do it. It takes advantage of the fact that you can
perform basic mathematical operations with the PasteSpecial method.

Sub Headcount()
Range("Final_HC").Value = Range("Starting_HC").Value
Range("Turnover").Value.Copy
Range("Final_HC").Value.PasteSpecial _
Paste:=xlValues, Operation:=xlSubtract
End Sub

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

 

[EVDV]

ROB, Thank you for a nifty trick, evdv