Uniquely find the maximum number in a range with VBA

[kelly mort]

My data starts from B5 and contains codes or ids like:
101
102
103
201
202
And so on.

So when I select a number in combobox1, say 1, I want to find the maximum number for the range of numbers having the first digit (from left) which matches the selection made in the combobox1.

Which in this case, will be 103.

Can someone help me out with it, using a MsgBox alert?

Thanks so much in advance.

 

[Micron]

How many values to look at? An array would likely be faster and less prone to hanging up your pc (not my forte in Excel) but depending on your version, there might be too many values for an array. Looping over the column would be another way, but from what I've read, this means going back and forth between the code and your sheet. Some say that's bad & I've never looked for evidence that this actually happens so I don't know.

If a formula like this was in a sheet column it might be quicker if not using an array as you could loop over that column and ignore everything that is zero.
=IF(VALUE(LEFT(E:E,1))=7,E:E,0) where EE contains the value you want to return and 7 is the combo value.
Sorry, no concrete idea at this point how you'd replace 7 with the combo value. Perhaps the combo updates a cell with the chosen value, so you use that cell reference instead?

 

[kelly mort]

Not more than 100 rows, for now.

This is how Iwas able to get it working my way:

For Each rC In rTab
     If indx = Left (rC, 1) Then 
         nMax = rC.Value
     End If 
Next rC

Since I am sorting in descending order, the last value in the nMax becomes the Maximum number in the range. And it works.

 

[Micron]

Left might return a string, and if that coerces your value to text, 11 will come before 2, for example.

 

[kelly mort]

Left might return a string, and if that coerces your value to text, 11 will come before 2, for example.

Oh okay. Then can you show me the array approach?

 

[kelly mort]

Not more than 100 rows, for now.

This is how Iwas able to get it working my way:

For Each rC In rTab
     If indx = Left (rC, 1) Then
         nMax = rC.Value
     End If
Next rC

Since I am sorting in descending order, the last value in the nMax becomes the Maximum number in the range. And it works.

I actually meant ascending order here. Instead of descending order.

 

[Micron]

Oh okay. Then can you show me the array approach?

I'd have to research it - it was brought to my attention 1 or 2 days ago here, but with 100 or so rows, I'd lean towards not using an array because that's what I'm more comfortable with. You might find it by doing an advanced search on my posts within the last 2 or 3 days, ignoring anything in Access threads. Sorry, I can't recall who posted the info about arrays.

You have a working method so maybe just watch your results for now? For only 100 or so rows I don't think I'd worry about array vs loop.