Find Function in VBA (Excel) won't find the minimum value

Question

I've been trying this a lot of times but I don't get this working. What I want to achieve: Find the minimum value in a specific column F:F and then get me the row number (or range address) where this value is.

Note: Variable rngMinimumVariableEins has to be reformat because the value is very small.

Dim rngMinimumVariableEinsWert As Range Dim strResultValue As String Dim rngMinimumVariableEins As Range Set rngMinimumVariableEins = Worksheets("Berechnung").Range("F:F") rngMinimumVariableEins.NumberFormat = "0.00000000000000000000000000" strResultValue = Application.WorksheetFunction.Min(rngMinimumVariableEins) Debug.Print strResultValue Set rngMinimumVariableEinsWert = rngMinimumVariableEins.Find(strResultValue, LookIn:=xlValues, LookAt:=xlPart) If rngMinimumVariableEinsWert Is Nothing Then Debug.Print "No Results" Else Debug.Print rngMinimumVariableEinsWert.Row End If

user10773628 · Accepted Answer · 2018-12-11 13:12:32Z

Excel worksheets have a 15 significant digit floating point limit and you can never reliably find a string that looks like a number in a column of true numbers.

dim minpos as variant with Worksheets("Berechnung").range("F:F") minpos = application.match(application.min(.cells.value2), .cells, 0) end with If iserror(minpos) Then Debug.Print "No Results" Else Debug.Print minpos End If

cybernetic.nomad · Accepted Answer · 2018-12-11 15:36:44Z

This is how you find the row number RowOfMin of the minimal value in a range RangeToSearch:

Option Explicit Public Sub FindMinimum() Dim RangeToSearch As Range Set RangeToSearch = Worksheets("Berechnung").Columns("F") Dim RowOfMin As Long On Error Resume Next With Application.WorksheetFunction RowOfMin = .Match(.Min(RangeToSearch), RangeToSearch, 0) End With On Error GoTo 0 If RowOfMin = 0 Then Debug.Print "No number values in column F" Else Debug.Print RowOfMin End If End Sub

Note that you must avoid converting the minimum value into another datatype (e.g. by writing it into a variable) and instead use it directly for the .Match otherwise you will run into floating point issues.

Collectives™ on Stack Overflow

Find Function in VBA (Excel) won't find the minimum value

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related