CODEWITHSUNDEEP
excelvba
Dergyll
Dergyll

Reputation: 949

Find first three occurrences in a list

I have a "PointsTally" worksheet with a list of names.

In the worksheet "LOG", I am attempting to

The following code is what I came up with, but it seems to put "Yes" in every cell in the column so my loop is failing. 

Sub loopnsub()

    Call sortlog  'Sortlog is a sub that sorts that sheet by descending date.

    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Dim name As Range
    Dim x As Long
    Dim iCol As Integer
    Dim iCol2 As Integer
    Dim i

    iCol = 2
    iCol2 = 7

    Set ws1 = Sheets("PointsTally")
    Set ws2 = Sheets("LOG")

    For Each name In ws1.UsedRange.Columns("B").Cells      
        For x = Cells(Cells.Rows.Count, iCol).End(xlUp).Row To 2 Step -1 
            For i = 1 To 3

                If Cells(x, iCol).Value = name.Value Then
                    Cells(x, iCol2).Value = "Yes"
                End If

            Next i            
        Next     
    Next c

End Sub

Upvotes: 0

Views: 168

Answers (1)

FAB
FAB

Reputation: 2569

Not sure what you are trying to do with For i = 1 To 3, is just rewriting to the same cell 3 times, so I've excluded that.

See if this helps:

Sub loopnsub()
    Call sortlog

    Const iCol As Integer = 2
    Const iCol2 As Integer = 7

    Dim wsPT As Worksheet: Set wsPT = Sheets("PointsTally")
    Dim wsLOG As Worksheet: Set wsLOG = Sheets("LOG")

    Dim lRowPT As Long: lRowPT = wsPT.Cells(Rows.Count, 2).End(xlUp).Row 'get last row in column B of "PointsTally"
    Dim lRowLOG As Long: lRowLOG = wsLOG.Cells(Rows.Count, iCol).End(xlUp).Row 'get last row in column [iCol] of "LOG"

    Dim Rpt As Long, Rlg As Long, C As Long, X As Long

    For Rpt = 1 To lRowPT 'for each row in "PointsTally"
        X = 0
        For Rlg = 2 To lRowLOG 'for each row in "LOG"
            If wsLOG.Cells(Rlg, iCol).Value = wsPT.Cells(Rpt, 2) Then
                wsLOG.Cells(Rlg, iCol2).Value = "Yes"
                X = X + 1
                If X = 3 Then Exit For 'check next row
            End If
        Next Rlg
    Next Rpt
End Sub

Upvotes: 1

Related Questions