Consulting

Results 1 to 5 of 5

Thread: i want required vba. I am looking for a small value from the Range

  1. #1

    i want required vba. I am looking for a small value from the Range

    Value Range Requirement Lowest Value By The Range
    25.000 1750.000 17.000
    17.000 1750.000 17.000
    33.000 1750.000 17.000
    18.000 1750.000 17.000
    33.000 1750.000 17.000
    47.000 1750.000 17.000
    66.000 1750.000 17.000
    21.000 1750.000 17.000
    41.000 1750.000 17.000
    98.000 3500.000 5.000
    45.000 3500.000 5.000
    70.000 3500.000 5.000
    5.000 3500.000 5.000
    53.000 3500.000 5.000
    55.000 3500.000 5.000
    47.000 3500.000 5.000
    12.000 3500.000 5.000
    15.000 5250.000 14.000
    66.000 5250.000 14.000
    14.000 5250.000 14.000
    45.000 5250.000 14.000
    68.000 5250.000 14.000
    21.000 5250.000 14.000
    75.000 5250.000 14.000
    77.000 5250.000 14.000
    89.000 5250.000 14.000
    17.000 5250.000 14.000
    Need VBA Codes.I am looking for a small value between Range
    Attached Files Attached Files

  2. #2
    Hi sanju,

    Try this.. This is not entirely mine. An expert from this forum helped me out with few weeks back. Hope this helps

    Sub test()
    Dim i As Long, strtrw As Long, endrw As Long
    i = 2
    strtrw = i
    endrw = i
     Do Until Cells(i, 1) = ""
            If Cells(i, 2) = Cells(i + 1, 2) Then
               endrw = i + 1
               
            Else
              Range(Cells(strtrw, 4), Cells(endrw, 4)) = Application.WorksheetFunction.Min(Range(Cells(strtrw, 1), Cells(endrw, 1)))
              strtrw = i + 1
              endrw = i + 1
              End If
              i = i + 1
     Loop
     
    End Sub
    .
    Last edited by kevvukeka; 07-28-2014 at 11:24 PM. Reason: typo error

  3. #3
    Thanks, But I want result shown as VBA Formula
    Ex. test(A2,B2)

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,247
    Location
    Public Sub GetMin()
    Const FORMULA_MIN = "=MIN(IF($B$2:$B$<lastrow>=$B2,$A$2:$A$<lastrow>))"
    Dim lastrow As Long
    
        With ActiveSheet
        
            lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
            .Range("C2").FormulaArray = Replace(FORMULA_MIN, "<lastrow>", lastrow)
            .Range("C2").AutoFill .Range("C2").Resize(lastrow - 1)
        End With
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5
    You are all the right to hang the sheet. This is no way to

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •