Adding Rabges without for each loops

[mdalamers via OfficeKB.com]

Hi,

I should like to add the cells in two ranges WITHOUT having to loop through
each range separately.
Suppose the cells A1 to A5 in my worksheet are filled, as well as cells B1 to
B5

Now I thought this would do the trick. But apparently not:
Sub AddRanges()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

Set r1 = Range("a1:a5")
Set r2 = Range("b1:b5")
Set r3 = Range("c1:c5")

r3.value = r1.value + r2.value
End Sub

Any suggestions?

 

[Jon Peltier]

Instead of adding the values, insert a formula:

ActiveSheet.Range("C1:C5").FormulaR1C1 = "=RC[-2]+RC[-1]"

- Jon

 

[L. Howard Kittle]

Try this, however I had to name the ranges on the worksheet to make it work.
Tried setting the range in code and it did not work...???

Sub AddRanges()
Dim Cell As Range
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim r1v As Integer
Dim r2v As Integer
Dim r3v As Integer

For Each Cell In Range("rng1")
r1v = r1v + Cell.Value
Next

For Each Cell In Range("rng2")
r2v = r2v + Cell.Value
Next

For Each Cell In Range("rng3")
r3v = r3v + Cell.Value
Next

MsgBox r1v + r2v + r3v

End Sub

HTH
Regards,
Howard

 

[Dave Peterson]

One more:

Option Explicit
Sub AddRanges()
Dim r1 As Range
Dim r2 As Range
Dim r3 As Range

Set r1 = Range("a1:a5")
Set r2 = Range("b1:b5")
Set r3 = Range("c1:c5")

r3.Value = r1.Value
r2.Copy
r3.PasteSpecial Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationAdd

Application.CutCopyMode = False

End Sub

It's the same as doing
select r1
edit|copy
select r3
edit|Paste special values (r1 to r3)

then
select r2
edit|copy
select r3
edit|Paste special|paste values|checking add

 

[Jon Peltier]

I meant to ask, what's wrong with looping?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

Instead of adding the values, insert a formula:

ActiveSheet.Range("C1:C5").FormulaR1C1 = "=RC[-2]+RC[-1]"

- Jon

 

[mdalamers via OfficeKB.com]

Thanks Guys.

This helped.
By the way Jon. Nothing is wrong with looping but I prefer using a few lines
rather than many lines.
Have a great weekend!

Michiel

 

[Jon Peltier]

Well, it should be a time-to-execute thing, not a number-of-lines thing. The
line count is pretty close. Looping through ranges can be very slow, of
course, but if you read the ranges into arrays, and write the results as an
array, it will avoid the slow cell-by-cell looping. It might even be faster
than Dave's interesting technique, because his has at least one extra
worksheet operation.

Sub AddRanges()
Dim v1 As Variant
Dim v2 As Variant
Dim v3(1 to 5, 1 to 1) As Variant
Dim i As Integer

' get worksheet data into VB arrays
v1 = Range("a1:a5").Value
v2 = Range("b1:b5").Value

' loop VB arrays to do the addition
For i = 1 to 5
v3(i, 1) = v1(i, 1) + v2(i, 1)
Next

' write VB array back to sheet
Range("c1:c5").Value = v3

End Sub

Slightly shorter, one less trip to the worksheet, but reading is faster than
writing:

Sub AddRanges2()
Dim vIn As Variant
Dim vOut(1 to 5, 1 to 1) As Variant
Dim i As Integer

' get worksheet data into VB arrays
vIn = Range("a1:b5").Value

' loop VB arrays to do the addition
For i = 1 to 5
vOut(i, 1) = vIn(i, 1) + vIn(i, 2)
Next

' write VB array back to sheet
Range("c1:c5").Value = vOut

End Sub

- Jon