I have two 3x3 (for simplicity's sake) ranges of cells; all cells in
each range contain plain ol' integers. I want to add the values of the
cells in Range A to the values of the cells in Range B and have the
sums become the new values of the cells in Range A.

I am learning VBA 'by ear' so I'm not very good, but long ago I
figured I could perform such an operation with SINGLE cells simply by
something like:

Range("A1").Value = Range("A1").Value + Range("A2").Value

However, when I made Range reference multiple cells...

Range("A1:C3").Value = Range("A1:C3").Value + Range("A4:C6").Value

...I got a type mismatch.

I then figured out that I could use Copy and PasteSpecial(xlValues) to
attain the desired effect, but in practice the ranges are large and
the calculations are repeated hundreds of times. This abuse of the
clipboard really wreaks havoc on system performance and I'm sure
there's a better way to do it.

I'd like specific examples of VBA code that would help me accomplish
the seemingly simple task of adding two ranges together. I imagine
there's more than one solution, and I'd like to see all possibilities,
but obviously the most streamlined approaches are preferred.

By the way, another thing that's been bugging me is the redundancy in
my examples. If you can show me a way to add two values without
mentioning the 'destination' range twice, I'd gladly toss in an extra
few bucks.

Thanks.

---

Clarification of Question by eller-ga on 22 Nov 2002 10:00 PST

Immediately after posting I stumbled onto the Consolidate method and
thought I had figured it out. Unfortunately, Consolidate apparently
won't work if the source and destination overlap. Still, it's very
close to what I need, so I suppose an alternate question would be if
there are any convenient ways around this limitation so that
Consolidate can be used.

Here are a couple of different ways to do this. The second way is much
more flexible. I recommend you use it for anything at all complicated.
It uses Excel's ability to refer to cells by row and column index. See
Excel Help for more information on the Cells collection.

Public Sub SumMe()
Dim xx As Integer

    For xx = 1 To 3
	' The following statement is all one line. 
        Range("A" & CStr(xx)).Value = Range("A" & CStr(xx)).Value +
Range("B" & CStr(xx)).Value + Range("C" & CStr(xx)).Value
    Next xx

End Sub

*****************************************************************

Public Sub SumMeAgain()
Dim intRow As Integer
Dim intCol As Integer
Dim intSum As Integer

    For intRow = 1 To 3
        intSum = 0
        For intCol = 1 To 3
            intSum = intSum + Cells(intRow, intCol)
        Next intCol
        Cells(intRow, 1).Value = intSum
    Next intRow

End Sub
****************************************************

Good luck with your project!

- Hammer