Xoolmonster

New Member
Joined
May 19, 2019
Messages
15
Hi guys,

I need help with a VBA macro that I’m supposed to finish for a course. The instructions for the macro is:

“Build a macro with a input box within a loop that asks the end user to put in numbers until the sum of numbers exceeds 100 then it ends and a message box appears which gives the sum of the numbers.”

How would one merits a macro like that?
 
Code:
Sub Loop_Test()
    Do Until iTotal > 100
        i = CInt(InputBox("Enter a number.", "Current Total = " & iTotal))
        iTotal = iTotal + i
    Loop
    MsgBox "Your final total is: " & iTotal, vbOKOnly, "Final Total"
End Sub

For the record, your original post was worded so that we assumed you simply wanted help accomplishing this yourself. Which is why nobody had provided the code for you at first. I think you had experienced coders eager to teach you to do this yourself. This is your homework after all. That being said, this accomplishes your task.

I really do hope you take the time to understand what was written for the sake of your education.
And I encourage you to ask questions about how this code works. We will be happy to help.

***As another note, I am not sure if you have any 'stability' requirements for this. But that code is very unstable as written (for example, entering anything other than a number in the input box will cause an error without some kind of data validation catch.***

Thank you so much Steve. that is very kind of you. I already understand the code and thank you again for your answer. I noticed your message a little late and already built up the following code myself,

Sub UntilHundred()
Do until x = 100
X = x + 1
Message = "Give a number "

Userinput = InputBox(Message)

Userinput = Val(Userinput)

Do Until Validnumber = True

MsgBox "Sum of the numbers is " & sum

Loop

End sub


I know that the loop is not right but anyway. thanks again...!
 
Upvote 0

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Thank you so much Steve. that is very kind of you. I already understand the code and thank you again for your answer. I noticed your message a little late and already built up the following code myself,

Sub UntilHundred()
Do until x = 100
X = x + 1
Message = "Give a number "

Userinput = InputBox(Message)

Userinput = Val(Userinput)

Do Until Validnumber = True

MsgBox "Sum of the numbers is " & sum

Loop

End sub


I know that the loop is not right but anyway. thanks again...!


This is a great attempt! However, it is definitely not going to accomplish your homework task. Would you like to work with us on it and we can help you fix it? There are a couple mistakes that if we resolve can fix your code without changing it too much.
 
Last edited:
Upvote 0
This is a great attempt! However, it is definitely not going to accomplish your homework task. Would you like to work with us on it and we can help you fix it? There are a couple mistakes that if we resolve can fix your code without changing it too much.

Sure, from what i understand i should define the variable X as the userinput is that right?
 
Upvote 0
Sub UntilHundred()

Do until x > 100

X = x + userinput

Message = "Give a number "

Userinput = InputBox(Message)

Userinput = Val(Userinput)

MsgBox "Sum of the numbers is " & sum

Loop

End sub
 
Upvote 0
Thank you so much Steve. that is very kind of you. I already understand the code and thank you again for your answer. I noticed your message a little late and already built up the following code myself,

Sub UntilHundred()
Do until x = 100
X = x + 1
Message = "Give a number "

Userinput = InputBox(Message)

Userinput = Val(Userinput)

Do Until Validnumber = True

MsgBox "Sum of the numbers is " & sum

Loop

End sub


I know that the loop is not right but anyway. thanks again...!


Code:
Sub UntilHundred()
    Do Until x = 100  ' This will only exit your loop if x = EXACTLY 100. So if it is 101,
                      ' the loop will continue. Forever.
   'Do Until x => 100 ' This will exit as soon as the number EQUALS 100 or is GREATER THAN 100.
        x = x + 1     ' This is not needed, however, it also guarentees the loop ends eventually
        Message = "Give a number " ' Not needed. Not wrong either.
        Userinput = InputBox(Message) ' This probably should have been userinput = InputBox("Give a number ")
                                      ' Still not wrong though. Just 'clunky' as it adds the uneeded line above.
        Userinput = Val(Userinput)    ' This works as a great way to validate the data.
        ' Now you need to write a line that adds Userinput to x
        Do Until Validnumber = True   ' This entire loop is not needed and will ALWAYS result in an
                                      ' infinite loop. Delete this.
            MsgBox "Sum of the numbers is " & Sum ' This should only be performed once.
                                                  ' & Sum should be replaced with & x
        Loop                          ' Not needed. Delete this too.
    Loop                              ' Move this above the MsgBox line.
End Sub
 
Last edited:
Upvote 0
You're almost there, but you need to move this line
Code:
[COLOR=#333333][I]X = x + userinput[/I][/COLOR]
after the inputbox.
Also the message box should be & x rather than & sum
 
Upvote 0
Code:
Sub UntilHundred()
    Do Until x = 100  ' This will only exit your loop if x = EXACTLY 100. So if it is 101,
                      ' the loop will continue. Forever.
   'Do Until x => 100 ' This will exit as soon as the number EQUALS 100 or is GREATER THAN 100.
        x = x + 1     ' This is not needed, however, it also guarentees the loop ends eventually
        Message = "Give a number " ' Not needed. Not wrong either.
        Userinput = InputBox(Message) ' This probably should have been userinput = InputBox("Give a number ")
                                      ' Still not wrong though. Just 'clunky' as it adds the uneeded line above.
        Userinput = Val(Userinput)    ' This works as a great way to validate the data.
        Do Until Validnumber = True   ' This entire loop is not needed and will ALWAYS result in an
                                      ' infinite loop. Delete this.
            MsgBox "Sum of the numbers is " & Sum ' This should onl be performed once.
                                                  ' & Sum should be replaced with & x
        Loop                          ' Not needed. Delete this too.
    Loop                              ' Move this above the MsgBox line.
End Sub

Sub UntilHundred()

Do Until x > 100

x = x + userinput

userinput = InputBox("Give a number ")

Userinput = Val(Userinput)

Loop

MsgBox "Sum of the numbers is " & x

End sub


Something like this right?
 
Upvote 0
Sub UntilHundred()

Do until x > 100

X = x + userinput

Message = "Give a number "

Userinput = InputBox(Message)

Userinput = Val(Userinput)

MsgBox "Sum of the numbers is " & sum

Loop

End sub

Code:
Sub UntilHundred()
    Do Until x > 100 ' Good.
        x = x + Userinput ' You need to move this down below userinput = val(userinput)
        Message = "Give a number " ' Good
        Userinput = InputBox(Message) ' Good
        Userinput = Val(Userinput) ' Good
        MsgBox "Sum of the numbers is " & Sum ' Move this outside of your loop.
                                              ' Also, what is 'sum'? I think you meant x
    Loop
End Sub

Almost there. see above.
 
Upvote 0
Sub UntilHundred()

Do Until x > 100

x = x + userinput

userinput = InputBox("Give a number ")

Userinput = Val(Userinput)

Loop

MsgBox "Sum of the numbers is " & x

End sub


Something like this right?

So in this case ´´x = x + userinput´´ stores the inputs given by the user and the program keeps asking the user for a number until the sum on the users inputs are bigger than 100
 
Upvote 0

Forum statistics

Threads
1,213,485
Messages
6,113,931
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top