Suming 2 named areas

[Carl Brehm]

Onhand & Received are 2 named areas on different worksheets.
Both areas are the same size. Need to add received to the onhand area cell
for cell.
Tried the following but returns error 1004
The other problem is the range can change so using x & y like this will only
work until I change the ranges.


Private Sub UpdateOnhand()
Dim X As Integer
Dim Y As Integer

X = 0
Y = 0

Do
Do
Range("ONHAND").Offset(X, Y).Value = Range("ONHAND").Offset(X,
Y).Value + Range("Received").Offset(X, Y).Value

Y = Y + 1
Loop Until Y = 119

X = X + 1

Loop Until X = 7

End Sub

also tried
Range("ONHAND").Value = Range("ONHAND").Value + Range("Received").Value

Thanks in advance
--
Carl & Linda Brehm
Lake Lafourche Bird House
Hebert, LA
Keets, Tiels, GN Lories, Quakers
Mitred Conures, TAG's, Bourkes
Cages

 

[Norman Jones]

Hi Carl,

Try:

Sub Tester()

Worksheets("Sheet1").Range("A1").Consolidate _
Sources:=Array("Onhand", "Received"), _
Function:=xlSum

End Sub

 

[Dave Peterson]

If I were doing it manually, I'd copy|paste special|and check that Add option
button.

In code:

Option Explicit
Sub testme()

Dim OnHandRng As Range
Dim ReceivedRng As Range

Set OnHandRng = Range("onhand")
Set ReceivedRng = Range("received")

If OnHandRng.Rows.Count <> ReceivedRng.Rows.Count _
Or OnHandRng.Columns.Count <> ReceivedRng.Columns.Count _
Or ReceivedRng.Areas.Count > 1 _
Or OnHandRng.Areas.Count > 1 Then
MsgBox "design error!"
Exit Sub
End If

ReceivedRng.Copy
OnHandRng.PasteSpecial operation:=xlPasteSpecialOperationAdd

Application.CutCopyMode = False

End Sub

You could even just resize the "sending" range and paste over the topleftcell of
the "receiving" range.

Option Explicit
Sub testme()

Dim OnHandRng As Range
Dim ReceivedRng As Range

Set OnHandRng = Range("onhand").Resize(1, 1)
Set ReceivedRng = Range("received")

ReceivedRng.Copy
OnHandRng.PasteSpecial operation:=xlPasteSpecialOperationAdd

Application.CutCopyMode = False

End Sub


Maybe one less thing to remember to fix.