VBA select case statements with variable set as relative cell giving off circular reference error-CodePudding

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

CodePudding user response：

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