Need Help on a Homework Assignment

[sliollio]

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!!!

 

[NeonRedSharpie]

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

 

[thatoneguy650]

This seems to meet your requirements

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

 

[BadgerRoad]

You should be able to so all this without VBA using data validation if you want to.

 

[sliollio]

Both work perfectly! You guys are awesome. (NeonRedSharpie: it's not for a grade if that helps ease your mind)

 

[sliollio]

Ahh son of a nutcracker 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 (I used NeonRedSharpies code)

 

[NeonRedSharpie]

Ahh son of a nutcracker 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 (I used NeonRedSharpies 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.