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

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

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

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

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

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

You could achieve the same result with in-cell formulae.
0
Excel 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
Author 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
Excel 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
IT Services ConsultantCommented:
(Thanks Glenn)
0
Author 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.