Results 1 to 5 of 5

Find Largest Values Greater than 'X' in VBA

Threaded View

  1. #1
    Registered User
    Join Date
    04-06-2012
    Location
    Charlotte, NC
    MS-Off Ver
    Excel 2010
    Posts
    7

    Find Largest Values Greater than 'X' in VBA

    Currently I have a pretty good macro that almost performs the task I want (see below). I would like for this macro to find all values greater than or equal to the value the user chooses in a particular cell.
    Right now the macro below is pulling the 15 largest values, (Dim lLarge(1 To 15) As Long) but I would like for it to only pull the values larger than the cell I have designated as losslimit (and I would like for the user to be able to change this number whenever he/she wishes).

    Note: The part of the macro that pulls the dates is working perfectly and does not need to be altered.
    I have attached the workbook that is the final product. I have filled in some dummy values already. To better explain, if the user put 50,000 into C15 (of WC Summary) the 'Details' table should show only values 600,000; 111,100; 100,000; & 50,000 (these values found in WC Input with arrays named WC_YearX). If the user were to change C15 to 200,000, the 'Details' table should only show 600,000.

    Sub Macro3()
    Dim losslimit As Long, j As Integer
    Dim r1 As Range, r2 As Range, r3 As Range, r4 As Range, r5 As Range, r6 As Range
    Dim k As Integer, m As Integer
    Dim r7 As Range, cfind As Range, ddate As Long
    Dim lLarge(1 To 15) As Long
    j = 1
    With Worksheets("WC Summary")
    losslimit = .Range("C15").Value
    End With
    With Worksheets("WC Input")
    Set r1 = Range(.Range("WC_Year1"), .Range("WC_Year1").End(xlDown))
    Set r2 = Range(.Range("WC_Year2"), .Range("WC_Year2").End(xlDown))
    Set r3 = Range(.Range("WC_Year3"), .Range("WC_Year3").End(xlDown))
    Set r4 = Range(.Range("WC_Year4"), .Range("WC_Year4").End(xlDown))
    Set r5 = Range(.Range("WC_Year5"), .Range("WC_Year5").End(xlDown))
    Set r6 = Range(.Range("WC_Year6"), .Range("WC_Year6").End(xlDown))
    k = 1
    For k = 1 To 15
    m = k
    lLarge(k) = WorksheetFunction.Large(Union(r1, r2, r3, r4, r5, r6), m)
    'MsgBox lLarge(k)
    If lLarge(k) > losslimit Then
    m = k
    Else
    m = m + 1
    End If
    lLarge(k) = WorksheetFunction.Large(Union(r1, r2, r3, r4, r5, r6), m)
    'MsgBox lLarge(k)
    Next k
    
    End With
    With Worksheets("WC Summary")
    For k = 1 To 15
    Set r7 = .Range("C18").Offset(k - 1, 0)
     r7 = lLarge(k)
    With Worksheets("WC Input")
    Set cfind = Union(r1, r2, r3, r4, r5, r6).Find(what:=lLarge(k), lookat:=xlWhole)
    If Not cfind Is Nothing Then ddate = cfind.Offset(0, -1)
    End With
    r7.Offset(0, -1) = ddate
    r7.Offset(0, -1).NumberFormat = "m/d/yyyy;@"
    Next k
    End With
    End Sub
    Attached Files Attached Files
    Last edited by Leith Ross; 04-11-2012 at 02:14 PM. Reason: Added Code Taqs

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1