How do I find min value of a range

Question

Having a problem with the worksheetfunction.min command at the end of this snippet. Values in the stated range are 8,9,10,1,12,12,12, so I would expect a return of 1. Actual return is 9 <

Dim strBegLookBackDO As String
Dim strCurLookBackDO As String
Dim CurColDO
Dim testvar1

       
        
        
For C = 2 To IntNumCol
'Set Alphabetic value from numeric
 CurColDO = Chr(C + 64)
    For R = 14 To IntNumRow
'following lines return B8 and B14, as they should   
        strBegLookBackDO = CurColDO & (R - 6)
        strCurLookBackDO = CurColDO & R
        Set A = Sheets("Composite").Range(strBegLookBack)
        Set B = Sheets("Composite").Range(strCurLookBack)
        Set Where = Worksheets("Composite").Range(A, B)
'following line returns an incorrect number
    testvar1 = WorksheetFunction.Min(Where)

Answer 1

• Avoid using VBA-reserved words or SQL-ish terms (e.g. Where) as variable names.
• Use descriptive names (rngStart, rngEnd, minValue) rather than single letters (A, B) or generic ones (testvar1).
• Always Dim everything explicitly to catch type-mismatches at compile time.

Sub FindMinInLookBack()
    Dim LastColumnIndex    As Long
    Dim LastRowIndex       As Long
    Dim columnIndex        As Long
    Dim rowIndex           As Long
    
    Dim columnLetter       As String
    Dim startCellAddress   As String
    Dim endCellAddress     As String
    
    Dim startCellRange     As Range
    Dim endCellRange       As Range
    Dim lookBackRange      As Range
    
    Dim minimumValue       As Double
    
    ' — set these as appropriate —
    LastColumnIndex = 5    ' e.g. check through column E
    LastRowIndex    = 20   ' e.g. check through row 20
    
    For columnIndex = 2 To LastColumnIndex
        columnLetter = Chr$(columnIndex + 64)
        
        For rowIndex = 14 To LastRowIndex
            ' build the two corners of the lookup range
            startCellAddress = columnLetter & (rowIndex - 6)
            endCellAddress   = columnLetter & rowIndex
            
            ' reference those cells on the Composite sheet
            With Sheets("Composite")
                Set startCellRange = .Range(startCellAddress)
                Set endCellRange   = .Range(endCellAddress)
                Set lookBackRange  = .Range(startCellRange, endCellRange)
            End With
            
            ' pull the minimum of that block
            minimumValue = Application.WorksheetFunction.Min(lookBackRange)
            
            ' do something with it (here we just debug-print)
            Debug.Print "Min in " & lookBackRange.Address(False, False) & " = " & minimumValue
        Next rowIndex
    Next columnIndex
End Sub

Answer 2

o You probably noticed in the meantime the issue with the different variable names strBegLookBackDO vs strBegLookBack. To avoid such errors, always use Option Explicit.

Also, you should always specify explicitly which datatype a variable has. In your case, CurColDO can only be a string, but you declare it as Variant. This is not an issue in 99% of the cases, but can give you a surprise when passing a variable as parameter to a routine.

o Dealing with characters in VBA to access the column of a range is a bad idea and not necessary. You logic of using Chr$(columnIndex + 64) will fail when you want to access the 27th column ("AA"). Instead, use cells - with that you can address a cell by row and column numbers.

o You should always tell VBA on which worksheet you want to work. In your case, you specify the worksheet (Sheets("Composite")) but not the workbook. In that case, VBA will look for the sheet Composite in the Active Workbook, but maybe that's not the workbook you want to use. If the sheet is in the same workbook as your code, use ThisWorkbook, eg Thisworkbook.Sheets("Composite")

o If Min doesn't return the value you expect, there are basically only 2 possibilities that could be wrong: Either the range is not correctly defined, or the values inside the range are not what you think they are. The Min-function will ignore values that are not numbers, eg strings, even if they look like numbers (a string 123 is something completly different than the number 123).

o I don't understand the reason why you are looping over the rows (your inner loop), but that's up to you. Currently, you loop over several rows (14 to LastRowIndex) and for every row (and column), you calculate the minimum of the cell and the 6 cells above - is that really what you want?

---

This is how my code would look like, assuming that lastRow and lastCol are defined or calculated somewhere.

Dim row As Long, col As Long
With ThisWorkbook.Sheets("Composite")
    For col = 2 To lastCol
        For row = 14 To lastRow
            Dim firstCell As Range, lastCell As Range, where As Range
            Set firstCell = .Cells(row - 6, col)
            Set lastCell = .Cells(row, col)
            Set where = .Range(firstCell, lastCell)
            testvar1 = WorksheetFunction.Min(where)
            Debug.Print where.Address, testvar1
        Next
    Next
End With