# Need Help on a Homework Assignment

#### sliollio

##### New Member
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

##### Well-known Member
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
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

##### Board Regular
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``````

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

#### sliollio

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

#### sliollio

##### New Member
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

##### Well-known Member
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)
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
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.