Evaluate sumifs in VBA

[jaffacake]

Good morning all,

I'm run into a bit of an issue with a workbook i'm writing.

I have a sumif formula pulling in indirect references which works when added to a cell in the workbook;

=SUMIFS(INDIRECT($C$16),INDIRECT(VLOOKUP("MONTH",$A$24:$C$28, 3,FALSE)),$F31,INDIRECT(VLOOKUP("Reporting Period",$A$24:$C$28, 3,FALSE)),$C$9)

This equates to;

=SUMIFS('HH Data'!$H$1:$H$1000000,'HH Data'!$D$1:$D$1000000,F31,'HH Data'!$F$1:$F$1000000,C9)

This formula is repeated on a monthly basis for a year i.e. F31 is April, F32 is May etc

I've tried to create this in VBA using the evaluate function as follows;

Sub Evaluate_All_Consumption()

Worksheets("HG - Elec").Select


With ActiveSheet


'1st Year


    'Meter address
    Meter_Address = .Range("C16").Value
    
    'Month address
    Month_Address = .Range("C26").Value
    
    'Reporting Period address
    Reporting_Period = .Range("C24").Value
    Reporting_Period_Query = .Range("C9").Value


    For x = 1 To 12
    
    Month_Query = .Range("F30").Offset(x, 0).Value


    Test_Value = Evaluate("=SumIfs(" & Meter_Address & ", " & Month_Address & ", " & Month_Query & ", " & Reporting_Period & ", " & Reporting_Period_Query & ")")
    
    Next x


End With


End Sub

However, rather than return the same value, the evaluated formula returns '0'. Where am i going wrong?

Thanks in advance for any help.

 

[JustynaMK]

Hi,

I think the solution might be as simple as adding some double quotes to your sumifs formula. Currently it presents:
=SumIfs('HH Data'!$H$1:$H$1000000, 'HH Data'!$D$1:$D$1000000, a, 'HH Data'!$F$1:$F$1000000, Q119)

In order to work properly, we need to include double quotes ("a" and "Q119"), so that it shows:
=SumIfs('HH Data'!$H$1:$H$1000000, 'HH Data'!$D$1:$D$1000000, "a", 'HH Data'!$F$1:$F$1000000, "Q119")

Try the following VBA code (you might need to adjust some Dim statements (datatypes), as I'm not sure what your source data looks like):

Sub Evaluate_All_Consumption()
    Dim Meter_Address               As String
    Dim Month_Address               As String
    Dim Month_Query                 As String
    Dim Reporting_Period            As String
    Dim Reporting_Period_Query      As String
    Dim x                           As Long
    Dim Test_Value                  As Long
    
    With Worksheets("HG - Elec")
    '1st Year
        'Meter address
        Meter_Address = .Range("C16").Value
        'Month address
        Month_Address = .Range("C26").Value
        'Reporting Period address
        Reporting_Period = .Range("C24").Value
        Reporting_Period_Query = .Range("C9").Value
        
        For x = 1 To 12
            Month_Query = .Range("F30").Offset(x, 0).Value
            Test_Value = Evaluate("=SumIfs(" & Meter_Address & ", " & Month_Address & ", """ & Month_Query & """, " & Reporting_Period & ", """ & Reporting_Period_Query & """)")
            Debug.Print "=SumIfs(" & Meter_Address & ", " & Month_Address & ", """ & Month_Query & """, " & Reporting_Period & ", """ & Reporting_Period_Query & """)"
            Debug.Print Test_Value
        Next x
    End With
End Sub

You can use the last two Debug.Print statements to see the final form of the adjusted formula. Simply go to VBA - open Immediate Window (Ctrl+G) - run the macro - analyze the results.

Hope it helps!