Mode function to show min. value when it's a tie

Hi,
I want VBA-code below to show lowest "mode-value" in a range.
I use "On error Resume next" for ranges with only single values.
rng4 is a vertical one column range

Please Login or Register  to view this content.

ie. If rng4 have numbers like: 1,2,2,3,3,5,5,6,7, I want MODE to show: 2
Is that possible?

Any help will be much appreciated.
Thanks in advance!

Hi,
Thanks for answer.

That's not a problem for Mode. Answer is 3.

For clairification.
If values are:
8,8,9,9,9,10,10,10,11
I want result to be 9.

I have about 95 columns to check.
One column have values: 3,2,2,7,9,5,3,1,7,5
There are four ties! 2x2, 2x3, 2x5 and 2x7
My code results in: 7
I want it to show: 2
2 is the lowest value of 2,3,5 and 7

Ok, but now I have to go. I will try to answer in about six hours or tomorrow.
Thanks a lot for your time.

Try

Please Login or Register  to view this content.

Thanks for your answers jindon and PCI

jindon
That line did the trick.
After implementing this line in my code, I encountered another problem.
I'm almost there but I can't figure it out how to solve it.

Range can be empty or up to hundreds of populated cells (no empty cells between numbers).
Range is now set up to be the eight last cells in each column.
I want Target10 to show the smallest, most frequently occurring number in the range.
If all numbers are single numbers, I want Target10 to show smallest number in range.

Example:
Range ("A32") = 8
Range("B14:B21"), numbers = 1,2,3,4,5,5,7,8
Target10 shows "1".
It should show "5".
Here is code:

Please Login or Register  to view this content.

Any idea how to fix that?
Thanks in advance!

WorksheetFunction.Min(WorksheetFunction.Mode_Mult(rng3)) should work as just Min if there is no duplicate, so

Please Login or Register  to view this content.

would be

Please Login or Register  to view this content.

That gave me:

Run-time error '1004':
Unable to get Mode_Mult property of the WorksheetFunction class

in first column without duplicate.
Column have numbers: 8,9,10,11,12,13,14,15,16 starting in row 14. Number 9 - 16 selected.

Ahhh, OK
How about

Please Login or Register  to view this content.

Same error at same column.
Line highlighted:
Target10.Value = WorksheetFunction.Min(WorksheetFunction.Mode_Mult(rng4))

That's impossible.
It should ignore the error.

Did you copy from the top,"On Error Resume Next"?

That's exactly what I did NOT do.
Works like a charm!!!

Considering this piece of code is just a part in a larger code, I wonder if "On Error Resume Next" can cause problems?

OK then

Please Login or Register  to view this content.

Thanks a lot jindon.
You are the best.

You are welcome and thanks for the rep.

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.