leeloo0505
Board Regular
- Joined
- Mar 28, 2003
- Messages
- 130
Hello, Trying to get the top 6 (where a 1 value is considered higher than 10)of a column that is not presorted or filtered.. I have the code below but it only goes through the column once producing only the top 2 when I want the top 6. I got some code from an archived post (thanks jimboy).
Here is my code:
Private Sub cmdQ12004_Click()
Dim c As Range
Dim rank As Long
Dim iQ1row As Long
Dim getrow As Long
Dim visiblerange As Range
Set visiblerange = Range("L6:L27").SpecialCells(xlCellTypeVisible)
iQ1row = Sheets("Q1").Range("N20").End(xlUp).Row + 1
rank = 6
For Each c In Range("L6:L27").SpecialCells(xlCellTypeVisible)
getcell = c.Address
If Len(getcell) = 5 Then
getrow = Right(getcell, 2)
Else
getrow = Right(getcell, 1)
End If
If c.Value = WorksheetFunction.Small(visiblerange, rank) Then
Sheets("Q1").Range("N" & iQ1row + rank) = Cells(getrow, 2).Value
rank = rank - 1
End If
Next c
End Sub
I am getting the row address cuz the value I want to put in ranking is not what is being evaluated, rather a persons name in the B(2) column.
so once c reaches 27 (where the column stops) it ends.. not relooping to give the other 4.
Thanks for your time,
leeloo0505
Here is my code:
Private Sub cmdQ12004_Click()
Dim c As Range
Dim rank As Long
Dim iQ1row As Long
Dim getrow As Long
Dim visiblerange As Range
Set visiblerange = Range("L6:L27").SpecialCells(xlCellTypeVisible)
iQ1row = Sheets("Q1").Range("N20").End(xlUp).Row + 1
rank = 6
For Each c In Range("L6:L27").SpecialCells(xlCellTypeVisible)
getcell = c.Address
If Len(getcell) = 5 Then
getrow = Right(getcell, 2)
Else
getrow = Right(getcell, 1)
End If
If c.Value = WorksheetFunction.Small(visiblerange, rank) Then
Sheets("Q1").Range("N" & iQ1row + rank) = Cells(getrow, 2).Value
rank = rank - 1
End If
Next c
End Sub
I am getting the row address cuz the value I want to put in ranking is not what is being evaluated, rather a persons name in the B(2) column.
so once c reaches 27 (where the column stops) it ends.. not relooping to give the other 4.
Thanks for your time,
leeloo0505