Sum Values In Two Ranges

Question

I am trying to add values in two ranges that have two rows of data.

Example:

The first part of macro locates the named range from the input into the target cell then copies that named range to the section adjacent to my target cell.

I don't know how to use what I already have, so that when a second input has been added under the original cell in the first part, to sum now the named ranges of both inputs like in the example above.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error GoTo ErrorHandler
    
    '1: FIRST LABEL VALUES
    If Not Intersect(Target, Range("J6:J7500")) Is Nothing Then
    
        Application.ScreenUpdating = False
    
        If Target = vbnulstring Then Exit Sub
    
        If Target.Column = 10 And Target.Offset(0, -1).Value > 0 Then
            
            'Find Named Range, Go To It and Copy It
            Dim NamedRange As Range
            Dim LabelCode As Range
            Dim name As String
            
            Range("BT2", Range("BT2").End(xlDown)).Select
            Set NamedRange = Selection
            Range("BS2", Range("BS2").End(xlDown)).Select
            Set LabelCode = Selection
            
            name = WorksheetFunction.Index(NamedRange, WorksheetFunction.Match(Target.Value, LabelCode, 0))
            
            Application.GoTo Reference:=ActiveWorkbook.Names(name).name
            
            Selection.Copy

            Target.Offset(-2, 3).Select
            Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                :=False, Transpose:=False
            Application.CutCopyMode = False
            
            'Apply % To New Numbers
            Dim rng As Range
            Dim myVal As Range

            Set rng = Selection

            For Each myVal In rng
                myVal = myVal.Value * Target.Offset(0, -1).Value
            Next myVal
            
            Target.Select

        End If
        
        Application.ScreenUpdating = True
        
    End If
    
    
    '2: SUM FIRST LABEL VALUES AND SECOND LABEL VALUES
    If Not Intersect(Target, Range("J6:J7500")) Is Nothing Then
    
        Application.ScreenUpdating = False
    
        If Target = vbnulstring Then Exit Sub
    
        If Target.Column = 10 And Target.Offset(-1, 0).Value <> "" Then
            
            'Find Named Range, Go To It and Copy It
            Dim NamedRange As Range
            Dim LabelCode As Range
            Dim name1 As String
            Dim name2 As String
            
            Range("BT2", Range("BT2").End(xlDown)).Select
            Set NamedRange = Selection
            Range("BS2", Range("BS2").End(xlDown)).Select
            Set LabelCode = Selection
            
            name1 = WorksheetFunction.Index(NamedRange, WorksheetFunction.Match(Target.Offset(-1, 0).Value, LabelCode, 0))
            
            Application.GoTo Reference:=ActiveWorkbook.Names(name1).name
            
            'Find Second Named Range And Add It To First Named Range
            name2 = WorksheetFunction.Index(NamedRange, WorksheetFunction.Match(Target.Value, LabelCode, 0))
            
            Application.GoTo Reference:=ActiveWorkbook.Names(name2).name
            
            'Add Values Together
            'HOW DO I DO THIS????
            
            'Apply % To New Numbers
            Dim rng As Range
            Dim myVal As Range

            Set rng = Selection

            For Each myVal In rng
                myVal = myVal.Value * Target.Offset(0, -1).Value
            Next myVal
            
            Target.Select

        End If
        
        Application.ScreenUpdating = True
        
    End If

ErrorHandler:
Exit Sub

Answer 1

If you have named ranges like:

• Range1 A2:E3
• Range2 A6:E7
• Outcome A10:E11

Just use an array formula:

Range("Outcome").FormulaArray = "=Range1+Range2"

or without named ranges:

Range("A10:E11").FormulaArray = "=A2:E3+A6:E7"