VBA Coding Help

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:

Attachment 22226

Thank you!

Maybe

Code:

---

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

---

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:

Code:

---


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

---

Quote:

---

Originally Posted by Paul_Hossler

Maybe

Code:

---

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

---

---

I ultimately ended up with this code:

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

---

Quote:

---

Originally Posted by 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:

Code:

---


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

---

---