PDA

View Full Version : [SOLVED] VBA Coding Help



TheTraveler
05-14-2018, 09:03 AM
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:

22226

Thank you!

Paul_Hossler
05-14-2018, 10:07 AM
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
05-14-2018, 12:36 PM
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
05-20-2018, 02:38 PM
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