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.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
873
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
@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.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

kelly mort

Well-known Member
Joined
Apr 10, 2017
Messages
1,989
Office Version
  1. 2016
Platform
  1. Windows
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.
Code:
lr = Sheet1.Cells (Rows.Count, "A").End (xlUp).Row
If lr < 4 Then lr = 4
 

Trebor76

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

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

Attachments

  • TLB.jpg
    TLB.jpg
    65.8 KB · Views: 2

Forum statistics

Threads
1,136,194
Messages
5,674,362
Members
419,504
Latest member
NeilG2021

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