Excel VBA Multiple Calculations Based On A single Cell Value

I Know what I need to accomplish, but I do not know how to write the correct code.
If E20 is "Yes", then 3 formulas need to run in 3 different cells.
If No, then it is just one formula in one cell. See below.

Private Sub Worksheet_Change(ByVal Target As Range)

‘FINAL PAY CALCULATOR

If Target.Address <> "$E$20" Then Exit Sub
If Target.Value = "Yes" Then (E21=G20), C28=G20 and C20=E20-(C21*C22)
ElseIf Target.Value = "No" Then (E21= C20+(C21*C22)
End If
End Sub

Any assistance or insight would be greatly appreciated

Tosagua
TosaguaAsked:
Who is Participating?
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.

Glenn RayExcel VBA DeveloperCommented:
This code will update the values in your ranges as appropriate.

Option Explicit
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
'FINAL PAY CALCULATOR

    If Target.Address <> "$E$20" Then Exit Sub
    If UCase(Target.Value) = "YES" Then
        Range("E21").Value = Range("G20").Value
        Range("C28").Value = Range("G20").Value
        Range("C20").Value = Range("E21").Value - (Range("C21").Value * Range("C22").Value)
    ElseIf UCase(Target.Value) = "NO" Then
        Range("E21").Value = Range("C20").Value + (Range("C21").Value * Range("C22").Value)
        Range("C20").Value = ""
        Range("C28").Value = ""
    End If
End Sub

Open in new window


Note that I added two lines to remove any existing values in C20 and C28 if "No" is chosen as an option.

Regards,
-Glenn
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
[ fanpages ]IT Services ConsultantCommented:
Hi,

Like Glenn, I have offered a solution below, but I think your requirements are flawed; specifically:

If Target.Value = "Yes" Then (E21=G20), C28=G20 and C20=E20-(C21*C22)

At this point cell [E20] will be "Yes".  The setting of cell [C20] will, therefore, not be a numeric value.

Note, also, that I have included the Application.EnableEvents = False... Application.EnableEvents = True statements to avoid multiple calls to the Worksheet_Change(...) event code when the [E1], [C28], & [C20] cell values are being set:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

'FINAL PAY CALCULATOR

  On Error Resume Next
  
  If Target.Address <> "$E$20" Then Exit Sub

  Application.EnableEvents = False
  
  If Target.Value = "Yes" Then
     Union([E1], [C28], [C20]).ClearContents
     [E1] = [G20]
     [C28] = [G20]
     [C20] = [E20] - ([C21] * [C22])
  ElseIf Target.Value = "No" Then
         [E21].ClearContents
         [E21] = [C20] + ([C21] * [C22])
  End If

  Application.EnableEvents = True
  
End Sub

Open in new window



Finally, does this have to be in Visual Basic for Applications [VBA] code?

You could achieve the same result with in-cell formulae.
0
Glenn RayExcel VBA DeveloperCommented:
^Good point about in-cell formulas.  That would be preferable.

I presumed that the reference to E20 in the formula was an error and replaced it with E21since it was mentioned in the ElseIf clause.
0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

TosaguaAuthor Commented:
fanpages,

The statement " The setting of cell [C20] will, therefore, not be a numeric value." really confused me. If all of the values in the formula C20=E20-(C21*C22) are numeric, how would the value of C20 not be numeric ?


An In-Cell formula is really a better option than a macro.

However, it appears that I would need stacked IF statements in cells E21, C28, and C20. But this doesn't seem to be working either. See attached.

Tosagua
Final-Pay-Calculator.xlsm
0
Glenn RayExcel VBA DeveloperCommented:
fanpages meant that, because cell E20 contains text (either YES or NO) it can't be used in a mathematical formula like your example:
C20 = E20-(C21*C22)

Your use of formulas in your example file is not correct Excel syntax.  One cannot assign another cell a value as you tried to do with a formula in this manner.  Each cell must have its own formula that responds to the value in E20.

See the attached example file that demonstrates this.  NOTE:  Your example shows cells in different locations than in your first post; off by one row.

Note also that it is no longer a macro-enabled file; it's not necessary.

-Glenn
EE-Final-Pay-Calculator.xlsx
0
[ fanpages ]IT Services ConsultantCommented:
(Thanks Glenn)
0
TosaguaAuthor Commented:
The formula strategy works perfectly,

Thank you for all of your assistance.

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

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.