Add and Subtract ranges (with multiple cells) in vba

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
Jon5001 said:
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
Click to expand...

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
 
Back
Top