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

Question

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

plz note, a minor clarification. my code ActiveCell.FormulaR1C1 = ... is looking to put the result of the selected sumif formula result back into the excel worksheet which is a numeric value. — mickey, Jan 22 '22 at 21:38
If I understand correctly, the custom function itself is defined in a cell like a normal formula, is that right? And your use of ActiveCell is meant to be the calling cell with the custom formula and you’re wanting to update that cell with the formula you define? Is my understanding correct? — Skin, Jan 22 '22 at 22:12
There can be many reasons why your function is giving a #value error, that is not at the function. I recommend you to copy your code at a procedure and run in a few cells, so you can debug it to check if it is a data issue, formula issue or function issue. — David García Bodego, Jan 23 '22 at 00:37
@Skin the custom function is meant to insert the answer from the case select arguments which are modified by the string value returned from another cell 5 columns to the left. make sense? — mickey, Jan 23 '22 at 15:54
@DavidGarcíaBodego thx tho i can confirm that it is it is a function issue — mickey, Jan 23 '22 at 15:55
Is this function only to be used in Column M on sheet "Schedule Updated" ? — CDP1802, Jan 23 '22 at 15:57
@CDP1802 yes, that is the ActiveCell that i am looking to insert the results of the formula chosen from the CASE SELECT statement chosen given the string on the same worksheet from column A. the sumifs take the appropriate value from a time series on another worksheet given two other criteria taken from the Schedule Updated worksheet. — mickey, Jan 23 '22 at 16:06
@Skin and the short answer to your question is yes. just wanted to clarify cuz i'm not sure my response was clear =) — mickey, Jan 23 '22 at 16:20

score 0 · Accepted Answer · answered Jan 23 '22 at 16:21

0

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

answered Jan 23 '22 at 16:21

CDP1802

13,871
2
7
17

perfect solution mate, thank you kindly! can you kindly remind me tho why Exit Function follows Case Else as opposed to going straight to End Select? thank you kindly for your help =))) – mickey Jan 23 '22 at 17:49
@mickey `Exit Function` to avoid the `pop_sched = WorksheetFunction..` as the c value is "" – CDP1802 Jan 23 '22 at 17:57
got it, thanks again i appreciate it! – mickey Jan 23 '22 at 18:49
@mickey If you wish to accept an answer the instructions are [here](https://stackoverflow.com/help/someone-answers) – CDP1802 Jan 23 '22 at 19:25

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

1 Answers1