How to find the smallest value in an array using vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
2,169
Office Version
  1. 2016
Platform
  1. Windows
Hello guys,

I am looking for a way to find the smallest value from an array of numbers like this:

ray = ("23, 4, 54, 55, 12, 67")

In the above, the smallest number is 4. So I want a code that can output the 4 in a MsgBox alert.

How do I get the work done?

Thanks in advance.
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
As presented, the variable ray is a string , not an array. Is that your intent?
 
Upvote 0
As presented, the variable ray is a string , not an array. Is that your intent?

I want it to be a number.

But when I leave the quotes out, it throws some error at me.
 
Upvote 0
Oops. I think I failed to understand your question properly.

Wait a minute let me give you a good response
 
Upvote 0
Hi,​
once you have numbers only within an array variable then you can use the Excel worksheet function MIN even under VBA …​
 
Upvote 0
I have this code that I want to run on multiple sheets and load the outcome of each sheet to a variable; either a string or an array, anyone that will work fine.


Code:
Sub GetMissingNum ()
Dim e&, i&, lr&, sh As Worksheet 

e = 0
Set sh = Sheet1.Cells (Rows.Count, "A").End (xlUp).Row
If lr < 4 Then lr = 4

With sh.Range("A4:A" & lr)
     Do 
        e = e + 1
     Loop Until IsError(Application.Match(e, .Cells, 0))

     MsgBox e 
End With 

End Sub

I am thinking of running the above code within a for loop like:

Code:
For Each sht In Worksheets 

Next sht

Then load whatever e returns into a variable as described above.

Then afterwards, I will want to get the smallest number that is stored.
 
Upvote 0
String
VBA Code:
Sub FindMinStr()
    Dim ray, MinVal, I, SA

    ray = ("23, 4, 54, 55, 12, 67")
    SA = Split(ray, ",")

    MinVal = CDbl(SA(LBound(SA)))
    For I = LBound(SA) To UBound(SA)
        If CDbl(SA(I)) < MinVal Then
            MinVal = CDbl(SA(I))
        End If
    Next I
    MsgBox "Min value is " & MinVal
End Sub

Array of numbers
VBA Code:
Sub FindMinArr()
    Dim ray, MinVal

    ray = Array(23, 4, 54, 55, 12, 67)
   
    MinVal = Application.Min(ray)
    MsgBox "Min value is " & MinVal
End Sub
 
Upvote 0
Solution
Hi Kelly,

You could use an ArrayList like so:

VBA Code:
Option Explicit
Sub Macro1()

    Dim objArr As Object
    Dim varItem As Variant

    Set objArr = CreateObject("System.Collections.ArrayList")
 
    For Each varItem In Array(23, 4, 54, 55, 12, 67)
        If objArr.Contains(varItem) = False Then
            objArr.Add varItem
        End If
    Next varItem
 
    With objArr
        .Sort 'Use objArr.Reverse for descending
        MsgBox .Item(0)
    End With

End Sub

Hope that helps,

Robert
 
Upvote 0
Hi @Trebor76, it's been a very long time. I miss you.

I had an error message on this line.
Code:
Set objArr = CreateObject("System.Collections.ArrayList")

Says automation error
 
Upvote 0
@rlv01,

All your codes worked fine. Thanks.

I will be glad if you could analyze what I posted concerning how I will be loading my numbers into the string or array
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top