Using Sums in Array Excel VBA

Question

I have an array that consists of static cells in variable workbooks. When I copy them to the active workbook, there are fewer cells than were in the original array. So 39 cells in the array being rolled into 33 cells as an example. Is there a way to sum the cells in the array like this?
Array("C6" + "C7","C8","C9")? Example Sheets

Source from external workbook:

Result in active workbook:

What I actually want result to look like: In my code I am trying to modify the cls array so "C6" would be "C6+C7" or something:

Option Explicit

Sub ImportData()

Dim B1 As Workbook
Dim B2 As Workbook
Dim S1 As Range
Dim cls As Variant, LR As Long, i As Long

Set B1 = ActiveWorkbook

cls = Array("C3", "C4", "C5", "C6", "C7", "C10", "C11", "C12", "G12")
With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Excel Files", "*.xlsx*;*.xlsm*;*.xlsa*;*.xm*"
    .AllowMultiSelect = False
    .Show
    If .SelectedItems.Count > 0 Then
        Workbooks.Open .SelectedItems(1)
        Set B2 = ActiveWorkbook
        Set S1 = Application.InputBox(prompt:="Select source sheet (select any cell)", Title:="Source sheet", Default:="A1", Type:=8)
        With B1.Sheets("Sheet2")
            For i = LBound(cls) To UBound(cls)
                .Range("C4").Offset(, i).Value = S1.Parent.Range(cls(i)).Value
            Next i
            .Range("C4:K4").EntireColumn.AutoFit
        End With
        B2.Close False
    End If
End With

End Sub

Thanks in advance.

score 1 · Accepted Answer · 2018-04-22 18:49:03Z

1

Here is a method that demonstrates four variations of describing ranges of cells to be used in a sum operation.

Option Explicit

Sub sumComplex()
    Dim i As Long, cls As Variant

    cls = Array("A1:A3", "B2,B4,B6", "C2:C3,C5", "D4")

    With Worksheets("sheet9")
        For i = LBound(cls) To UBound(cls)
            Debug.Print Application.Sum(.Range(cls(i)))
        Next i
    End With
End Sub

'results
 6 
 12 
 20 
 99

This could be applied to your code in the following manner.

cls = Array("C3", "C4", "C5", "C6:C7", "C10", "C11", "C12,G12")
...
    With B1.workSheets("Sheet2")
        For i = LBound(cls) To UBound(cls)
            if S1.Parent.Range(cls(i)).count > 1 then
                .Range("C4").Offset(0, i).Value = _
                    application.sum(S1.Parent.Range(cls(i)))
            else
                .Range("C4").Offset(0, i).Value = S1.Parent.Range(cls(i))
            end if
        Next i
        .Range("C4").resize(1, i).EntireColumn.AutoFit
    End With

edited Apr 22, 2018 at 18:49

answered Apr 22, 2018 at 6:05

user4039065

Sign up to request clarification or add additional context in comments.

8 Comments

user4039065 Over a year ago

tbh, I found your example code incomplete and a bit confusing; hence my generalized response. Best guess would be .Range("B7").Offset(, i) = Application.Sum(TargetSummSheet.Parent.Range(cls(i)))

user4039065 Over a year ago

Please read minimal reproducible example. Without an mcve, there is simply too much guesswork.

user4039065 Over a year ago

Look at my response above. Anyone can take the code and sample data then step through the code to achieve the provided results. Now look at your question and note what is missing.

user4039065 Over a year ago

Well, actually, it should work but that is not correct syntax for a sum function. In fact c12+g12 could also be used as evaluate(cls(i))

user4039065 Over a year ago

... and Range("C12+G12") is not a valid reference.

|

Collectives™ on Stack Overflow

Using Sums in Array Excel VBA

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related