none
Row number RRS feed

  • Question

  • Hello ,

    I calculate the minimum difference between two numbers with this code , did anyone know how can I get the row number that give the min ?

    Sub min_diff()
    Dim rng As Range
    Dim Dn  As Integer
    Dim Rwrng As Range
    Dim Rw As Range
    Dim c As Long
    
    Set rng = Range(Range("C1"), Range("C" & Rows.Count).End(xlUp))
    ReDim ray(1 To rng.Count ^ 2, 1 To 3)
    Dn = 6
    
    For Each Rw In rng
            If Not Dn = Rw Then
                    c = c + 1
                    ray(c, 1) = Dn
                    ray(c, 2) = Rw
                    ray(c, 3) = Abs(Dn - Rw)
                End If
            Next Rw
    
    Dim num As Double
    Dim n As Long
    
    num = Format(Application.Min(Application.Index(ray, Evaluate("Row(1:" & c & " )"), 3)), "0.00")
    
    For n = 1 To c
        If Format(ray(n, 3), "0.00") = num Then
            MsgBox "Min value Nums = " & vbCrLf & ray(n, 1) & ", " & ray(n, 2) & " = " & ray(n, 3)
        End If
    Next n
    End Sub
    
    

    Best regards,

    Neven


    • Edited by Req_En Wednesday, July 4, 2018 12:22 PM
    Wednesday, July 4, 2018 12:20 PM

Answers