How to find the smallest value in an array using vba

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

rlv01

Well-known Member
Joined
May 16, 2017
Messages
873
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
As presented, the variable ray is a string , not an array. Is that your intent?
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
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.
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
Oops. I think I failed to understand your question properly.

Wait a minute let me give you a good response
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,129
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Hi,​
once you have numbers only within an array variable then you can use the Excel worksheet function MIN even under VBA …​
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
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.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
873
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

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
 
Solution

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,755
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
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
 

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
@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
 

Forum statistics

Threads
1,136,207
Messages
5,674,415
Members
419,508
Latest member
trinstrick

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
Top