Formula's in command Button
Formula's in command Button
(OP)
I hate to keep asking questions, but need some help on Formula writing inputed in a command button. I have never done this and seek your professional help.
I have 25 cells that have percentages for Markup puposes depending on selection.
e.g. 2.2L ( placed in Cell R2) is selection and therefore, mark up figure is 1.25. 2.3P (Placed in Cell R3) is a selection and therefore, mark up figure is 1.4. etc.
Now I have created a drop down box with 2.2L, 2.2P.... in cell B79. So now what I want to do is when you select one of the drop down choices, eg. 2.2L it automatically will calculate the figure in H51 using the formuala given to 2.2L. If selecting 2.2P it will automatically change the figure according to that formula.
Currently in in H51 the formula is =B51*C51*IF(Q51="MOH OK",1,1.1346)*IF(R51="$",1,1.29).
I hope I didn't totally confuse the heck out of all you, but let me know if i need to explain it better.
Thanks again everyone..
Bill
I have 25 cells that have percentages for Markup puposes depending on selection.
e.g. 2.2L ( placed in Cell R2) is selection and therefore, mark up figure is 1.25. 2.3P (Placed in Cell R3) is a selection and therefore, mark up figure is 1.4. etc.
Now I have created a drop down box with 2.2L, 2.2P.... in cell B79. So now what I want to do is when you select one of the drop down choices, eg. 2.2L it automatically will calculate the figure in H51 using the formuala given to 2.2L. If selecting 2.2P it will automatically change the figure according to that formula.
Currently in in H51 the formula is =B51*C51*IF(Q51="MOH OK",1,1.1346)*IF(R51="$",1,1.29).
I hope I didn't totally confuse the heck out of all you, but let me know if i need to explain it better.
Thanks again everyone..
Bill





RE: Formula's in command Button
RE: Formula's in command Button
Private Sub CommandButton1_Click()
If Sheets(1).Cells(79, 2) = "" Then
MsgBox "Please select Organization Type, Then re-run Calculation."
ElseIf Sheets(1).Cells(79, 2) = "2.2P - Helsinki" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2))
ElseIf Sheets(1).Cells(51, 17) = "Add MOH" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2)) * 1.1346
ElseIf Sheets(1).Cells(51, 18) = "Euro" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2)) * Sheets(2).Cells(16, 5) * Sheets(2).Cells(16, 6)
End If
End Sub
RE: Formula's in command Button
ElseIf and If are not handled the same way. If an ElseIf condition is true, it will execute the code following 'then' but will not continue the next ElseIf stament, it will exit the If. I think you may want something more like
Private Sub CommandButton1_Click()
If Sheets(1).Cells(79, 2) = "" Then
MsgBox "Please select Organization Type, Then re-run Calculation."
ElseIf Sheets(1).Cells(79, 2) = "2.2P - Helsinki" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2))
If Sheets(1).Cells(51, 17) = "Add MOH" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2)) * 1.1346
End If
If Sheets(1).Cells(51, 18) = "Euro" Then
Sheets(1).Cells(51, 8) = Sheets(1).Cells(51, 2) * (Sheets(1).Cells(51, 3)) * (Sheets(2).Cells(16, 2)) * Sheets(2).Cells(16, 5) * Sheets(2).Cells(16, 6)
End If
End If
End Sub
Notice I chaned your last two ElseIf statements to If statements. This way they will get checked if the first ElseIf evaluates true
RE: Formula's in command Button
Step 1. It will check Cell 79,2 and calculate the fomula and input it into cell 51,8.
Step 2. Now if MOH drop down box is selected in Cell 51,17, it will calculate whats in cell 51.8 (first calculation/formula) and multyply it by 1.1346.(problem is if you deselect it, will it put it back to Step 1?
Step 3. Then the same thing for the Euro Selection... Select Cell 51.8 and multiply it by the eruo. (problem is if you deselect it, will it put it back to Step 1?
Now going back to the begining... If cell 79.2 is "Nagasaki" then (like Step 1)it will calculate the formula and input it in Cell 51,8. Note I have 20 selections...
Hope this explanation helps a little more. Wondering if just a plain formula would be better, whats your thoughts on that?
RE: Formula's in command Button
RE: Formula's in command Button