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
Bookmarks