Consulting

Results 1 to 10 of 10

Thread: Add Roundup formula to VBA button

  1. #1

    Add Roundup formula to VBA button

    Hello!
    Newby here looking to add a simple Roundup function in a VBA userform but it appears that this isn't as easy as Excel. I have a simple formula wherein a/b needs to be rounded up no matter what. 112/10=11.2...round up to 12. A and b could be any numbers as they are dependant upon whatever the user enters on the userform. I have a button that says "calculate", that once pressed, needs to run this formula. Please help! Thank you so much in advance!!

  2. #2
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    427
    Location
    Hi Ryanchris!
    If they are all positive numbers, I usually use the int method like below.
    Of course, application.roundup is also correct.
    Sub test()
    Dim a, b
    a = 112
    b = 10
    'MsgBox Application.RoundUp(a / b, 0)
    MsgBox -Int(-a / b)
    End Sub
    

  3. #3
    I will give that a try! I'll let you know how it goes. Thank you!

  4. #4
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    298
    Location
    And what about the case when b = 0?
    I only give food for thought.

    Artik

  5. #5
    VBAX Mentor 大灰狼1976's Avatar
    Joined
    Dec 2018
    Location
    SuZhou China
    Posts
    427
    Location
    Hi Artik!
    It's going to be a mathematical error not a method error when b = 0.

  6. #6
    Quote Originally Posted by 大灰狼1976 View Post
    Hi Ryanchris!
    If they are all positive numbers, I usually use the int method like below.
    Of course, application.roundup is also correct.
    Sub test()
    Dim a, b
    a = 112
    b = 10
    'MsgBox Application.RoundUp(a / b, 0)
    MsgBox -Int(-a / b)
    End Sub

  7. #7
    Worked perfectly. Thank you all so much! Huge help for this newby...

  8. #8
    Quick correction...it works perfectly unless no data is entered. See the code below. HQX = Text box where the answer is recorded. HFX = first data point, 100 for example. HCL is always the same number between 2 and 20 - whatever the user selects.
    If the user skips the HF1 box and enters data in the HF2 box, the code breaks. If there is a number in every HFX box, the code works perfectly.
    Private Sub Hcalculate_Click()
    'MsgBox Application.RoundUp(a / b, 0)
    HQ1 = -Int(-HF1 / HCL)
    HQ2 = -Int(-HF2 / HCL)
    HQ3 = -Int(-HF3 / HCL)
    HQ4 = -Int(-HF4 / HCL)
    HQ5 = -Int(-HF5 / HCL)
    HQ6 = -Int(-HF6 / HCL)
    HQ7 = -Int(-HF7 / HCL)
    HQ8 = -Int(-HF8 / HCL)
    HQ9 = -Int(-HF9 / HCL)

  9. #9
    VBAX Tutor
    Joined
    Dec 2008
    Posts
    298
    Location
    Private Sub Hcalculate_Click()
        Dim i           As Long
    
        On Error Resume Next
        For i = 1 To 9
            With Me.Controls("HQ" & i)
                .Value = vbNullString
                .Value = -Int(-CDbl(Me.Controls("HF" & i).Value) / CDbl(HCL.Value))
            End With
        Next i
        On Error GoTo 0
    
    End Sub
    First, each of the HQx controls will be cleared, and then a calculation attempt will be made. If any of the HFx or HCL fields are empty, an error will occur that was "handled" with the On Error Resume Next command, which instructs you to ignore runtime errors.

    Artik

  10. #10
    That is brilliant. Works perfectly! Thank you!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •