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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.