VBA Coding Help

[TheTraveler]

Hello,

I am still very new to VBA.

Could somebody show me how to do the following:

Write a procedure that shows the smallest positive number in the selected data range. If there are no positive numbers, your procedure should show the message: "There are no positive values".

I'd like to get a pop-up box that looks like that from another problem we were working on:

Capture1.jpg

Thank you!

[Paul_Hossler]

Maybe

Option Explicit

Sub Something()
    Dim c As Range
    Dim n As Long
    
    If Not TypeOf Selection Is Range Then Exit Sub

    n = 0
    
    For Each c In Selection.Cells
        If c.Value > 0 Then
            If n = 0 Then
                n = c.Value
            ElseIf n > c.Value Then
                n = c.Value
            End If
        End If
    Next
    
    If n = 0 Then
        MsgBox "There are no positive values", vbOKOnly, "Microsoft Excel"
    Else
        MsgBox n, vbOKOnly, "Microsoft Excel"
    End If
End Sub

[TheTraveler]

Thank you! That helped me get started. I made a few alterations so that it would work with non-integers as well. And I deleted some of the code related to the message box:

Sub SmallestPositiveNumber()
Dim c As Variant
Dim n As Variant
    
If Not TypeOf Selection Is Range Then Exit Sub


n = 0
    
For Each c In Selection.Cells
    If c.Value > 0 Then
        If n = 0 Then
            n = c.Value
        ElseIf n > c.Value Then
            n = c.Value
        End If
    End If
Next
    
    If n = 0 Then
        MsgBox "There are no positive values"
    Else
        MsgBox n
    End If
End Sub

Maybe

Option Explicit

Sub Something()
    Dim c As Range
    Dim n As Long
    
    If Not TypeOf Selection Is Range Then Exit Sub

    n = 0
    
    For Each c In Selection.Cells
        If c.Value > 0 Then
            If n = 0 Then
                n = c.Value
            ElseIf n > c.Value Then
                n = c.Value
            End If
        End If
    Next
    
    If n = 0 Then
        MsgBox "There are no positive values", vbOKOnly, "Microsoft Excel"
    Else
        MsgBox n, vbOKOnly, "Microsoft Excel"
    End If
End Sub

[TheTraveler]

I ultimately ended up with this code:

Sub SmallestPositiveNumber()
    
If Not TypeOf Selection Is Range Then Exit Sub

n = 0
    
For Each c In Selection.Cells

    If c.Value > 0 Then
        If n = 0 Or n > c.Value Then
            n = c.Value
        End If
        
End If
Next
    
    If n = 0 Then
        MsgBox ("There are no positive values")
    Else
        MsgBox (n)
    End If
    
End Sub

Thank you! That helped me get started. I made a few alterations so that it would work with non-integers as well. And I deleted some of the code related to the message box:

Sub SmallestPositiveNumber()
Dim c As Variant
Dim n As Variant
    
If Not TypeOf Selection Is Range Then Exit Sub


n = 0
    
For Each c In Selection.Cells
    If c.Value > 0 Then
        If n = 0 Then
            n = c.Value
        ElseIf n > c.Value Then
            n = c.Value
        End If
    End If
Next
    
    If n = 0 Then
        MsgBox "There are no positive values"
    Else
        MsgBox n
    End If
End Sub