Return results of sumifs with indirect references formula in vba

[jaffacake]

Hi all,

I have a workbook that i'm using to present analysis of a lot of data. Currently i'm using a sumifs formula with indirect references within the workbook and it's returning the expected value. However, the workbook is very slow and i'm looking to speed it up and make it more responsive. I'm therefore looking at removing the formula from the workbook and have the formula run through vba instead. I can't however get the coding right.

Any thoughts would be welcome!

Sub Add_Data_HH()


Column_Ref = ActiveSheet.Range("D7").Value
Report_Month = ActiveSheet.Range("D8").Value
Report_Year = ActiveSheet.Range("D9").Value
Data_Source = ActiveSheet.Range("D10").Value


'Oldest


With ActiveSheet


y = 36


For x = 1 To 12
    
    Query_Month = .Range("k" & y).Value
    Query_Year = .Range("i34").Value
    
    .Range("i" & y).Value = SumIfs(INDIRECT("'" & Data_Source & "'!" & Column_Ref), INDIRECT("'" & Data_Source & "'!$B:$B"), Query_Year, INDIRECT("'" & Data_Source & "'!$C:$C"), Query_Month)
    
    y = y + 1
    
Next x


End With


End Sub

 

[jaffacake]

Hi all,

Having slept on this I've made progress but it's created another unexpected problem.

Here is my code;

Private Sub Add_Data_HH()

Column_Ref = ActiveSheet.Range("D7").Value
Report_Month = ActiveSheet.Range("D8").Value
Report_Year = ActiveSheet.Range("D9").Value
Data_Source = ActiveSheet.Range("D10").Value


'Oldest


With ActiveSheet


y = 36


For x = 1 To 12
    
    Query_Month = "K"
    Query_Year = "I34"
    
     '.Range("i" & y).Formula = "=SumIfs('" & Data_Source & "'!" & Column_Ref & ",'" & Data_Source & "'!$C:$C," & Query_Year & ",'" & Data_Source & "'!$B:$B," & Query_Month & y & ")"
     .Range("i" & y) = [=SumIfs('" & Data_Source & "'!" & Column_Ref & ",'" & Data_Source & "'!$C:$C," & Query_Year & ",'" & Data_Source & "'!$B:$B," & Query_Month & y & ")]
 
    y = y + 1
    
Next x


End With


End Sub

If i run the code to add the formula i get the expected results but if i use the square brackets to evaluate the formula it returns #Value !. What am i doing wrong?

Any help would be appreciated.

Kind regards,

 

[jkpieterse]

The square brackets syntax requires the final formula to be between them. But you could do it like this instead:

For x = 1 To 12
    
    Query_Month = "K"
    Query_Year = "I34"
    
     .Range("i" & y).Formula = "=SumIfs('" & Data_Source & "'!" & Column_Ref & ",'" & Data_Source & "'!$C:$C," & Query_Year & ",'" & Data_Source & "'!$B:$B," & Query_Month & y & ")"
     .Range("i" & y).Value = .Range("i" & y).Value
 
    y = y + 1
    
Next x

 

[FormR]

if i use the square brackets to evaluate the formula it returns #Value !. What am i doing wrong?

Hi, you can't use the shorthand method if you are dynamically building the string, instead you need to use:

.Range("i" & y) = Evaluate("=SumIfs('" & Data_Source & "'!" & Column_Ref & ",'" & Data_Source & "'!$C:$C," & Query_Year & ",'" & Data_Source & "'!$B:$B," & Query_Month & y & ")")

 

[wideboydixon]

I think you need to adapt that code a little. Give this a try:

Private Sub Add_Data_HH()

Dim thisRow As Long

Column_Ref = Range("D7").Value
Report_Month = Range("D8").Value
Report_Year = Range("D9").Value
Data_Source = Range("D10").Value

For thisRow = 36 To 47
    Cells(thisRow, "I").Value = Application.WorksheetFunction.SumIfs(Sheets(Data_Source).Range(Column_Ref), Sheets(Data_Source).Range("$C:$C"), Range("I34").Value, Sheets(Data_Source).Range("$B:$B"), Cells(thisRow, "K").Value)
Next thisRow

End Sub

WBD

 

[jaffacake]

Thanks very much everyone! Greatly appreciated.