Excel VBA trying to find the address of the smallest value among a list of values

Question

I am new to Excel VBA programming and I ran into problem when trying to use the Find method to capture the address of the smallest value among a list of values. The following is my code and I have included 3 screenshots to illustrate the problem. Any input is appreciated, thanks in advance!!

Sub Max_Min_Test()

    Dim Largest As Long
    Dim Smallest As Long
    Dim FirstPlace As Range
    Dim LastPlace As Range

    Largest = WorksheetFunction.Max(Range("A2:A8"))
    Smallest = WorksheetFunction.Min(Range("A2:A8"))

    Range("A10").Value = Largest
    Range("A11").Value = Smallest

    Set FirstPlace = Range("A2:A8").Find(what:=Largest)
    Set LastPlace = Range("A2:A8").Find(what:=Smallest)

    Range("B10").Value = FirstPlace.Address
    Range("B11").Value = LastPlace.Address

End Sub

In the first screenshot, cell A10 and A11 showed the largest and smallest value from A2:A8, and Cell B10 and B11 showed the address of the largest and smallest value from the list. In this case, the addresses were correct.

In the second screenshot, cell B10 displayed the address of the largest value correctly but cell 11 displayed the wrong address. Instead of A8, A7 was displayed as the address of the smallest value.

In the third screenshot, cell B10 still displayed the address of the largest value correctly but cell B11 also displayed the same address. It seemed to me that my code would not capture the address of the smallest value but the address of the value with the same first digit as the smallest value...

May I just say it's refreshing to see a first-post that includes a clear explanation, useful screenshots, and a minimal reproducible example! — Chronocidal
You need to set lookAt parameter of Range.Find to xlWhole — Samuel Hulla

Chronocidal Chronocidal · Accepted Answer · 2018-09-22T19:51:42

If we take your second example:

You set the value of smallest to 7
You search for 7
The first cell containing 7 is 72 in $A$7

Checking the Range.Find documentation, you'll see that there is an optional LookAt parameter - this can be xlWhole or xlPart - this is currently defaulting to xlPart, so includes cells which contain a 7 anywhere in them. (If not included, it remembers the last option you used.)

So, try these lines:

Set FirstPlace = Range("A2:A8").Find(what:=Largest, LookAt:=xlWhole)
Set LastPlace = Range("A2:A8").Find(what:=Smallest, LookAt:=xlWhole)

Excel VBA trying to find the address of the smallest value among a list of values

1 Answers