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
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.