Need Help on a Homework Assignment

sliollio

New Member
Joined
Sep 8, 2014
Messages
22
Working on something for class.
Instructions are as follows:

  1. Ask the user for a positive integer (num), below 100.
  2. If the user enters a negative number or greater than 100, give a warning and ask to re-enter.
  3. Generate 20 positive random numbers between (1- num).
  4. Display the generated random numbers in the range (A1, A20)

Here's what I have so far

Sub RandomNumb()
Dim Low As Double
Dim High As Double
Low = 1
High = InputBox("Enter a positive integer below 100 (1-100)")
r = Int((High - Low + 1) * Rnd() + Low)
MsgBox (r)
If High > 100 Then
MsgBox ("Error")
High = InputBox("Enter a positive integer below 100 (1-100)")
r = Int((High - Low + 1) * Rnd() + Low)
MsgBox (r)
End If
If Number <= 0 Then
MsgBox ("Error")
High = InputBox("Enter a positive integer below 100 (1-100)")
r = Int((High - Low + 1) * Rnd() + Low)
MsgBox (r)
End If
End Sub

Obviously quite a few issues but not sure how to fix them. I don't know how to get it to loop the error message and re-enter box. Also not sure how to get it to display the results on my worksheet instead of a message box. As well as how to get it to perform this task exactly 20 times.

Any help is greatly appreciated!!!
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.
Code:
Sub IShouldntHelpWithHomework()

    Dim number As Integer
    
On Error GoTo errHandle
    
tryAgain:
    
    number = InputBox("Enter a positive number between 1 and 100", "Input a number")
    
    Select Case number
        Case Is < 1
            MsgBox "Please enter a number greater than 0."
            GoTo tryAgain
        Case Is > 100
            MsgBox "Please enter a number less than 100."
            GoTo tryAgain
        Case Is <= 100, Is >= 0
        Case Else
            MsgBox "Please re-enter your value."
            GoTo tryAgain
    End Select
    
    For x = 1 To 20
        ActiveSheet.Cells(x, 1).Value = _
            Application.WorksheetFunction.RandBetween(1, number)
    Next x
        
Exit Sub


errHandle:


MsgBox "Please enter a number, not a character."
GoTo tryAgain


End Sub
 
Upvote 0
This seems to meet your requirements

Code:
Sub mrExcelInputBox()

Dim other As Integer


Do While i <> 1
    Value = Application.InputBox("Enter a positive integer below 100 (1-100)", , , , , , , 2)
        If Value = False Then
            Exit Sub
        End If
If Value = "" Then
    MsgBox "No number entered"
ElseIf Int(Val(Value)) <> Value Then
    MsgBox "Non Integer. Please enter another number"
ElseIf Val(Value) <= 0 Then
    MsgBox "Below range. Please enter another number"
ElseIf Val(Value) > 100 Then
    MsgBox "Above range. Please enter another number"
Else
    i = 1
End If
Loop
other = Val(Value)
For x = 1 To 20
    ActiveSheet.Cells(x, 1).Value = Application.WorksheetFunction.RandBetween(1, other)
Next x


End Sub
 
Upvote 0
You should be able to so all this without VBA using data validation if you want to.
 
Upvote 0
Both work perfectly! You guys are awesome. (NeonRedSharpie: it's not for a grade if that helps ease your mind:rolleyes:)
 
Upvote 0
Ahh son of a nutcracker:eek: Turned the page and there is a part 2 >.<

Consider integer numbers in the range (A1, A20) as generated in the previous exercise. Find the following:

  • Max, min (using one loop)
  • Find the second max
  • Average (using a while loop)
  • Standard deviation (using a do until loop)
  • Reduce the standard deviation under a threshold

No worries if y'all don't want to help anymore! I'm probably going to go to office hours for help on this stuff but IF by chance anyone wants to help again I wouldn't oppose:biggrin: (I used NeonRedSharpies code)
 
Upvote 0
Ahh son of a nutcracker:eek: Turned the page and there is a part 2 >.<

Consider integer numbers in the range (A1, A20) as generated in the previous exercise. Find the following:

  • Max, min (using one loop)
  • Find the second max
  • Average (using a while loop)
  • Standard deviation (using a do until loop)
  • Reduce the standard deviation under a threshold

No worries if y'all don't want to help anymore! I'm probably going to go to office hours for help on this stuff but IF by chance anyone wants to help again I wouldn't oppose:biggrin: (I used NeonRedSharpies code)

Code:
Sub IShouldntHelpWithHomework()

    Dim number As Integer
    Dim minVal As Integer
    Dim maxVal As Integer
    Dim newMax As Integer
    
    Dim nextRow As Integer
    Dim counter As Integer
    Dim averageSum As Integer
    Dim average As Integer
    
On Error GoTo errHandle
    
tryAgain:
    
    number = InputBox("Enter a positive number between 1 and 100", "Input a number")
    
    Select Case number
        Case Is < 1
            MsgBox "Please enter a number greater than 0."
            GoTo tryAgain
        Case Is > 100
            MsgBox "Please enter a number less than 100."
            GoTo tryAgain
        Case Is <= 100, Is >= 0
        Case Else
            MsgBox "Please re-enter your value."
            GoTo tryAgain
    End Select
    
    For x = 1 To 20
        ActiveSheet.Cells(x, 1).Value = _
            Application.WorksheetFunction.RandBetween(1, number)
    Next x
        
    minVal = 101
    maxVal = 0
        
    For x = 1 To 20
        If Cells(x, 1) > maxVal Then maxVal = Cells(x, 1)
        If Cells(x, 1) < minVal Then minVal = Cells(x, 1)
    Next x
    
    newMax = 0
    
    For x = 1 To 20
        If Cells(x, 1) <> maxVal And Cells(x, 1) > newMax Then
            newMax = Cells(x, 1)
        End If
    Next x
    
    nextRow = 1
    counter = 0
    
    Do While Cells(nextRow, 1) <> ""
        averageSum = averageSum + Cells(nextRow, 1)
        counter = counter + 1
        average = averageSum / counter
        nextRow = nextRow + 1
    Loop
        
    MsgBox "Min Value = " & minVal & vbNewLine & _
            "Max Value = " & maxVal & vbNewLine & _
            "Second Max = " & newMax & vbNewLine & _
            "Average = " & average
            
        
Exit Sub


errHandle:


MsgBox "Please enter a number, not a character."
GoTo tryAgain


End Sub


I'm not sure why you'd ever use a loop for max/min/average. I can't even come close to figuring out std dev with a loop.
 
Upvote 0

Forum statistics

Threads
1,213,562
Messages
6,114,326
Members
448,564
Latest member
ED38

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