Formula's in command Button 2

[Harley78]

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

 

[Harley78]

oh i forgot to mention... not only am I trying to calculate cell H51 depending on cell B79,, this will occur with Cells H52, H53 and so on... also O52,,,,, and so on.

 

[Harley78]

ok, after six hours, here is what I came up with so far, but can't get the MOH and Euro Line working. Any suggestions what I might be doing wrong?

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

 

[CFSEng]

Well I'm a little confused as to what you are trying to do. It appears you may have your If statements off a bit, but maybe not. Make sure your logic is correctly being applied.

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

 

[Harley78]

Thanks a bunch, I will check that out.... I figured my post would be confusing and sorry about that. What my plans are for this 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?

 

[Harley78]

Oh and about 18 cells (51,8 52,8 53,8 so on)

 

[Harley78]

Oh, by the way, it did work the way you have it... Sometimes I just have a brain dump and think to hard... many thanks again