Tosagua
asked on
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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
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
![Avatar of [ fanpages ]](https://filedb.experts-exchange.com/files/public/2020/2/28/4acbe344-900b-4e79-96ce-65fe58a6f004.png){kind=small}
(Thanks Glenn)
ASKER
The formula strategy works perfectly,
Thank you for all of your assistance.
Tosagua
Thank you for all of your assistance.
Tosagua
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.