Add and Subtract ranges (with multiple cells) in vba

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

"Jon5001" <[email protected]> wrote in message
news:[email protected]...
> 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