I am looking to utilize the user defined formula below to create a variable (cell_val) that is compared across a number of select case statements to return a specific sumif function result.
the only variable that changes in the sumif function given the cell_val value (which is a string) is the sum column of the sumif function.
I am getting a circular reference / #value error however and was hopeful some of you kind folks may have some suggestions that you can provide me =))
Function pop_sched()
Dim cell_val As String
cell_val = ActiveCell.Offset(0, -5).Value
Select Case cell_val
Case "1.Provision_Net_Revenue"
ActiveCell.FormulaR1C1 = "=SUMIFS(stage3!C[-7],stage3!C[-12],'schedule updated'!RC[-12],stage3!C[-11],'schedule updated'!RC[-8])"
Case "2.Credit_Losses_PCL"
ActiveCell.FormulaR1C1 = "=SUMIFS(stage3!C[-9],stage3!C[-12],'schedule updated'!RC[-12],stage3!C[-11],'schedule updated'!RC[-8])"
Case "3.Trading_Losses"
ActiveCell.FormulaR1C1 = "=SUMIFS(stage3!C[-10],stage3!C[-12],'schedule updated'!RC[-12],stage3!C[-11],'schedule updated'!RC[-8])"
Case "9.Tax"
ActiveCell.FormulaR1C1 = "=SUMIFS(stage3!C[-8],stage3!C[-12],'schedule updated'!RC[-12],stage3!C[-11],'schedule updated'!RC[-8])"
Case Else
ActiveCell.Value = 0
End Select
End Function
In cell M4 put =pop_sched(H4)
Function pop_sched(cell As Range)
Dim c As String
Dim ws As Worksheet
Set ws = Sheets("stage3")
Select Case cell.Value
Case "3.Trading_Losses": c = "C"
Case "2.Credit_Losses_PCL": c = "D"
Case "9.Tax": c = "E"
Case "1.Provision_Net_Revenue": c = "F"
Case Else
pop_sched = 0
Exit Function
End Select
pop_sched = WorksheetFunction.SumIfs(ws.Columns(c), ws.Columns("A:A"), _
cell.Offset(, -7), ws.Columns("B:B"), cell.Offset(, -3))
End Function