excel - Display the lowest value

Question

Code Description : Using For Next write VBA macro to accept ten numbers and display (through message box) total, average, highest and lowest

Problem : Only if 1 is in the selection it shows the lowest value otherwise blank result.

Sub Codes()

    For counter = 1 To 10
        ActiveCell = InputBox("Please enter 10 numbers", " ")
        vartotal = vartotal + ActiveCell
        varaverage = vartotal / counter
        
        If ActiveCell > varhighest Then varhighest = ActiveCell
        If ActiveCell = 1 Then varlowest = ActiveCell Else If ActiveCell < varlowest Then varlowest = ActiveCell
    
        ActiveCell.Offset(1, 0).Select
    Next counter
        MsgBox "SumTotal = " & vartotal & vbNewLine & "Average = " & varaverage & vbNewLine & "Highest = " & varhighest & vbNewLine & "Lowest = " & varlowest
End Sub

question from:https://stackoverflow.com/questions/65928944/display-the-lowest-value

Answer 1

Another method is to load an array then just use the worksheet formulas:

Sub Codes()
    Dim counter As Long
    Dim activecell(9) As Double
    Dim vartotal As Double
    Dim varhighest As Double
    Dim varlowest As Double
    Dim varaverage As Double

    For counter = 0 To 9
        activecell(counter) = Application.InputBox("Please enter 10 numbers", " ", , , , , , 1)
    Next counter
    vartotal = Application.Sum(activecell)
    varhighest = Application.Max(activecell)
    varlowest = Application.Min(activecell)
    varaverage = Application.Average(activecell)
    MsgBox "SumTotal = " & vartotal & vbNewLine & "Average = " & varaverage & vbNewLine & "Highest = " & varhighest & vbNewLine & "Lowest = " & varlowest
End Sub