SumIf in inactive worksheet and storing as array element

Viewed 42

My aim is to SumIf in an inactive sheet, store resulting values as array elements and then transpose the array into ThisWorkBook, keeping to minimal visual and processing speed disruption.

Code:

Option Explicit
Option Base 1

Sub BM_Rebal()
    
    Application.ScreenUpdating = False
    Application.Calculation = xlManual

    ' Variables
    
        Dim wb1, wb2, wb3 As Workbook
        Dim ws1, ws2, ws3 As Worksheet
        Dim wsf As WorksheetFunction
        Dim Arr1(1 To 22), Arr2(1 To 22), Arr3(1 To 22), Arr4(1 To 22) As Variant
        Dim i, j, k As Integer
        Dim A, B, Path1, Path2 As String
        Set wsf = Application.WorksheetFunction
        Set wb3 = ThisWorkbook
        Set ws3 = wb3.Sheets("Currencies")
        i = 1
        A = Format(ws3.Cells(1, 4), "yyyymmdd")
        B = Format(ws3.Cells(1, 3), "yyyymmdd")
        Path1 = "[string]" & _
            "[string]" & A & ".csv"
        Path2 = "[string]" & _
            "[string]" & B & ".csv"
        Set wb1 = Workbooks.Open(Path1)
        Set wb2 = Workbooks.Open(Path2)
        Set ws1 = wb1.Sheets("[string]" & A)
        Set ws2 = wb2.Sheets("[string]" & B)
    
    ' Body

        wb3.Activate
        'wb1.Activate
        
        With ws1
            k = .UsedRange.Columns("BF").Rows.Count
            For i = 1 To 22
                Arr1(i) = wsf.SumIfs(.Range(.Cells(3, 58), .Cells(k, 58)), _
                                    .Range(.Cells(3, 2), .Cells(k, 2)), ws3.Cells(1, 1), _
                                    .Range(.Cells(3, 68), .Cells(k, 68)), ws3.Cells(i + 2, 1))
            Next i
            For j = i To 22
                Arr2(j) = 100 * ( Arr1(j) / wsf.Sum(Arr1) )
            Next j
            ws1.Close
        End With
    
        'wb2.Activate
        
        With ws2
            k = .UsedRange.Columns("BF").Rows.Count
            For i = 1 To 22
                Arr3(i) = wsf.SumIfs(.Range(.Cells(3, 58), .Cells(k, 58)), _
                                    .Range(.Cells(3, 2), .Cells(k, 2)), ws3.Cells(1, 1), _
                                    .Range(.Cells(3, 68), .Cells(k, 68)), ws3.Cells(i + 2, 1))
            Next i
            For j = 1 To 22
                Arr4(j) = 100 * ( Arr3(j) / wsf.Sum(Arr3) )
            Next j
            ws2.Close
        End With

    ' Output

        With ws3
            .Range(.Cells(3, 3), .Cells(24,3)) = Application.Transpose(Arr2)
            .Range(.Cells(3, 4), .Cells(24,4)) = Application.Transpose(Arr4)
        End With
        
    Application.ScreenUpdating = True
    Application.Calculation = xlAutomatic

End Sub

Which 1004 errors here:

Arr1(i) = wsf.SumIfs(.Columns("BF3:BF" & k), .Columns("B3:B" & k), .Cells(1, 1), .Columns("BP3:BP" & k), .Cells(i + 2, 1))

Now, given the msg provides no further detail other than the alert of what error type is occurring, the most likely cause was the position of (in)active sheets, but activating - bringing the relevant sheets forwards - proved unsuccessful and the syntax seems to be referencing the objects properly anyway, without the need to activate (which is ugly).

What is causing this error please?

1 Answers

.Columns("BF3:BF" & k) (to take one instance) should be .Range("BF3:BF" & k)

You need to use Range here instead of Columns, since you're not working with an entire column, but a subset of the column.

One can do Columns(1) or Columns("A") or Columns("A:A"), but not Columns("A1:A10").

Related