Edmond
07-08-2019, 02:18 PM
Hello All,
My end goal would be to find the 3 (or more) biggest values within a range while applying conditions.
I am using the worksheetFunction.Large currently but of course without filters.
I thought about creating an UDF with a loop but as my set of data is quite massive I am afraid it takes a long time to process.
Did some of you face that issue or would know a way to solve it?
Thanks a lot!!
This is what I have done:
(it works but just wanted to know if other solutions exist)
With wsRD
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set rg = Range(.Cells(1, 1), .Cells(LastRow, LastCol + 3))
For i = 1 To LastRow
If .Cells(1 + i, 8) = "70" Then
If .Cells(1 + i, 43) = "NewLate" Then
If .Cells(i + 1, 24).Value > lRank Then
lRank = .Cells(i + 1, 24).Value
End If
End If
End If
Next
End With
MsgBox lRank
My end goal would be to find the 3 (or more) biggest values within a range while applying conditions.
I am using the worksheetFunction.Large currently but of course without filters.
I thought about creating an UDF with a loop but as my set of data is quite massive I am afraid it takes a long time to process.
Did some of you face that issue or would know a way to solve it?
Thanks a lot!!
This is what I have done:
(it works but just wanted to know if other solutions exist)
With wsRD
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
LastCol = .Cells(1, Columns.Count).End(xlToLeft).Column
Set rg = Range(.Cells(1, 1), .Cells(LastRow, LastCol + 3))
For i = 1 To LastRow
If .Cells(1 + i, 8) = "70" Then
If .Cells(1 + i, 43) = "NewLate" Then
If .Cells(i + 1, 24).Value > lRank Then
lRank = .Cells(i + 1, 24).Value
End If
End If
End If
Next
End With
MsgBox lRank