# How to find the smallest value in an array using vba

#### kelly mort

##### Well-known Member
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?

### 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
As presented, the variable ray is a string , not an array. Is that your intent?

#### kelly mort

##### Well-known Member
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
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

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
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

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``````

#### Trebor76

##### Well-known Member
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
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
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
@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

Replies
10
Views
172
Replies
3
Views
244
Replies
1
Views
31
Replies
9
Views
213
Replies
30
Views
699

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.

### Which adblocker are you using?

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

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