VBA Conditional If (Should Be Simple, Maybe?)

Hello EE folks,
I am not well versed in VBA and I am having trouble getting the following code to work correctly.  I need to change the formula in two cells (actually more than 2 cells, but I can expand) when the value in K2 is changed.  The code just seems to run a long time and only changes the formula in the first cell referenced.


Private Sub Worksheet_Calculate()
   
    Dim term As Integer
    term = Range("K2").Value

    If term = 10 Then
    Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
   
    ElseIf term = 10 Then
    Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""].&[""&RC1&""]"")"
   
    ElseIf term = 20 Then
    Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
   
    ElseIf term = 20 Then
    Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""].&[""&RC1&""]"")"

    Else
   
End If


End Sub
Lindsay_KAsked:
Who is Participating?
 
Rob HensonFinance AnalystCommented:
Don't know anything about Cubes so can't answer that.

Can't really tell with what you have pasted above, are the lines for putting the formulas into H23 and H32 separated by a carriage return?

Try switching calculation off at the beginning of the code and then switching it back on after.

Switch off:
Application.Calculation = xlCalculationManual

Switch On:
Application.Calculation = xlCalculationAutomatic

Thanks
Rob
0
 
Rob HensonFinance AnalystCommented:
You don't need the ElseIf between the two formula statements.

If term = 10
Range(H23) Formula
Range(H32) Formula

ElseIf Term = 20
Range(H23) Formula
Range(H32) Formula

End If

By having the ElseIf between the formula statements it is skipping the following line and going to the End If.

Thanks
Rob H
0
 
Lindsay_KAuthor Commented:
Thank you Rob,

I updated the code to reflect the following:

Private Sub Worksheet_Calculate()
   
    Dim term As Integer
    term = Range("K2").Value

    If term = 10 Then
    Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
    Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""].&[""&RC1&""]"")"
   
    ElseIf term = 20 Then
    Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
    Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""].&[""&RC1&""]"")"

    Else
   
End If


End Sub


However, H23 updates and then just flashes repeatedly for a few minutes and H32 does not get updated.  Do you think this is due to the formulas being cubes?

Thank you again,

Lindsay
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Lindsay_KAuthor Commented:
I'm not sure about what you mean exactly, but I found if I removed the .&[""&RC1&""] from the end of the two lines for H32, and if I put the code into Worksheet_Activate (), then the code works!  However, of course, in order to make it run, I have to click on another sheet in the workbook and then click the sheet the code runs on.  I really would like this to run when the K3 cell is updated?  Do you think this might be possible Rob, or should I open a different question?

(For Reference, the following VBA does work)
Private Sub Worksheet_Activate()

Dim term As Integer
     term = Range("K2").Value

     If term = 10 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"
     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
     
     ElseIf term = 20 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"
     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"

     Else
     
   
End If


End Sub


Thank you,

Lindsay
0
 
Rob HensonFinance AnalystCommented:
By removing that small bit from the formula, does it still look right once populated?
0
 
Lindsay_KAuthor Commented:
yes
0
 
Rob HensonFinance AnalystCommented:
For the calculation suggestion
Private Sub Worksheet_Activate()

Application.Calculation = xlCalculationManual

Dim term As Integer
     term = Range("K2").Value

     If term = 10 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&RC1&""]"")"

     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - FALL GROUP].&[""&$A23&""]"")"
     
     ElseIf term = 20 Then
     Range("H23").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[cohort_cde].&[""&R2C7&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&RC1&""]"")"

     Range("H32").Formula = "=CUBEVALUE(""PowerPivot Data"",""[Measures].[Distinct Count of ID_NUM]"",""[Table_TMSEPRD].[COHORT YEAR GROUP].&[""&$C$9&""]"",""[Table_TMSEPRD].[COHORT TERM GROUP].&[""&$J$2&""]"",""[Table_TMSEPRD].[NR - SPRING GROUP].&[""&$A23&""]"")"

End If
Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

Thanks
Rob
0
 
Lindsay_KAuthor Commented:
With Rob's guidance after removing the unnecessary ElseIf's, I was able to determine that removing the .&[""&RC1&""] from the end of the two lines for H32, and if putting the code into Worksheet_Activate () provided a solution.

Thank you again Rob :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.