×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Formula's in command Button
2

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


RE: Formula's in command Button

(OP)
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.

RE: Formula's in command Button

(OP)
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

RE: Formula's in command Button

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

RE: Formula's in command Button

(OP)
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?




     

RE: Formula's in command Button

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

RE: Formula's in command Button

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources