How to find the smallest value in an array using vba

[kelly mort]

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.

 

[rlv01]

@rlv01,

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

The code you posted is not making sense to me. Your objective is unclear. The value of lr is indeterminate, and the code crashes be cause you are treating sh as a range variable. I'm not following what it has to do with the original question of finding the lowest number in an array.

 

[kelly mort]

The code you posted is not making sense to me. Your objective is unclear. The value of lr is indeterminate, and the code crashes be cause you are treating sh as a range variable. I'm not following what it has to do with the original question of finding the lowest number in an array.

Oops. Sorry that was a typo.

It should be this.

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

 

[Trebor76]

it's been a very long time. I miss you.

Thanks Kelly - you haven't missed much. Hope all is well with you and yours in the land of the long white cloud.

Not sure why the code fails but at a guess it could be that the mscorlib.tlb file is missing (refer here).

The great thing about using an ArrayList in my humble opinion is that it has a built in sorting method for both numbers and strings so you don't have to use another procedure to do it for you.

Cheers,

Robert

 

[kelly mort]

Thanks Kelly - you haven't missed much. Hope all is well with you and yours in the land of the long white cloud.

Not sure why the code fails but at a guess it could be that the mscorlib.tlb file is missing (refer here).

The great thing about using an ArrayList in my humble opinion is that it has a built in sorting method for both numbers and strings so you don't have to use another procedure to do it for you.

Cheers,

Robert

You are right. It seems the file is not executing

 

[Trebor76]

You are right. It seems the file is not executing

That's a shame. You'll have to use rlv01's nifty code then.