Selecting Variable's Location (as Cell) in Excel VBA

Question

So, i have a problem about selecting the exact cell of a formulated value. Im really new to VBA and i searched the internet for atleast 2 days for a valuable answer. Here let me show you the code im trying to put and explain what it does afterwards;

    Dim r As Range
    MinTime = WorksheetFunction.Min(Range("C5:L14"))
Set r = ActiveSheet.Cells.Find(MinTime)
r.Select
MinRow = ActiveCell.Row
MinCol = ActiveCell.Column

So what im trying to do is to find the minimum number in a range with worksheetfunction.min and select the cell which i 've just found. Then direct the row and column information to other 2 variable. But this code is not working always. Sometimes it works just fine sometimes it doesnt and i am clueless at the moment. Any kind of help would be appreciated. Thank you already.

Answer 1

I suspect that your range is actually containing times. The code below will find the earliest time (the lowest value in the range).

Private Sub MinTime()

    Dim Rng As Range
    Dim Mini As Range
    Dim MiniValue As Double
    Dim MinRow As Long, MinClm As Long

    Set Rng = ActiveSheet.Range("C5:L14")

    MiniValue = Application.Min(Rng)
    With Rng
        Set Mini = .Find(What:=MiniValue, _
                         After:=.Cells(1), _
                         LookIn:=xlFormulas, _
                         LookAt:=xlWhole, _
                         SearchOrder:=xlByRows, _
                         SearchDirection:=xlNext, _
                         MatchCase:=False, _
                         MatchByte:=False)
    End With

    With Mini
        MinRow = .Row
        MinClm = .Column
    End With
End Sub

The trick is to LookIn:=xlFormulas instead of the default xlValues. You may enter 12:00 but the cell's formula is 0.5. Presuming that to be the minimum in the range, when you look for 0.5 in the values you won't find it because the cell's "value" in this context (confusion by Microsoft, not my doing) is "12:00". Therefore you would have to convert the minimum value found back into a string, like "12:00", or you look for the 0.5 in the formulas.

Answer 2

You can even do this without using the WorksheetFunction or the Find function. Making an array and then looping through it is a fairly easy way to find what you are looking for. Try this

Sub findMin(ByVal minRange As Range)
Dim searchArr() As Range
ReDim searchArr(1 To minRange.Rows.Count * minRange.Columns.Count) As Range
Dim minVal As Double
Dim i As Integer, i2 As Integer, outputRow As Integer, outputColumn As Integer
For i2 = 0 To minRange.Columns.Count
    For i = 0 To minRange.Rows.Count
        searchArr(i + 1) = Cells(minRange.Row + i, minRange.Column + i2).Value
    Next i
Next i2

    minVal = searchArr(1).Value
    For i = 1 To UBound(searchArr)
        If searchArr(i).Value < minVal Then
            minVal = searchArr(i).Value
            outputRow = searchArr(i).Row
            outputColumn = searchArr(i).Column
        End If
    Next i
End Sub

Answer 3

In your question's code, you're using the Find method on all of the worksheet's cells. As @Gary's Student's answer shows, you should invoke the Find method on the same range as the one within which you've found the Min.

Also, there are several parameters to the Find method that will make a difference, such as looking for whole values or not, looking within formulas or not, looking for a particular format or not. You can easily find information about the Find method on the 'net.

Here's my take on your code:

Sub MyAssignment()
    Dim searchRange As Range
    Dim minCell As Range
    Dim minTime As Double
    Dim minRow As Long
    Dim minCol As Long

    Set searchRange = ActiveSheet.Range("C5:L14")
    minTime = WorksheetFunction.Min(searchRange)

    Set minCell = searchRange.Find(minTime, searchRange.Cells(1), XlFindLookIn.xlValues, XlLookAt.xlWhole, MatchCase:=False, SearchFormat:=False)
    minCell.Select

    minRow = minCell.Row
    minCol = minCell.Column
End Sub

The parameters you use when invoking the Find method from VBA will be mirrored on the Find dialog as seen from Excel (Ctrl+F). Conversely, changing options on Excel's Find dialog will impact your next VBA's Find call because the parameters you don't supply to the Find method will take the values last used on the dialog.

Let us know how it turns out.