Excel vba with nested loops keeps giving incorrect counts

I have a network dataset where each student (ego) is supposed to select up to 10 close friends. I have used a macro to do the following:

For each ego in column A, search for its occurrence in columns B to K (which indicates a nomination by a friend)
For every occurrence of the ego in columns B to K, compare its class and SEN with the person who nominated him/her via a vlookup list (columns M,N,O).
Then, output the counts in columns Q to X. The headers represent the permutations of friendship nominations. For instance, "YY_SC" inidicates being selected by a student from the same class("SC") and SEN ("YY").

Macro:

Sub CompareData()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim egoRow As Range
    Dim friend1 As Range
    Dim friend2 As Range
Dim friend3 As Range
Dim friend4 As Range
Dim friend5 As Range
Dim friend6 As Range
Dim friend7 As Range
Dim friend8 As Range
Dim friend9 As Range
Dim friend10 As Range

    Dim egoClass As String
    Dim egoSEN As String
    Dim friend1Class As String
    Dim friend1SEN As String
    Dim friend2Class As String
    Dim friend2SEN As String
   Dim friend3Class As String
    Dim friend3SEN As String
Dim friend4Class As String
    Dim friend4SEN As String
Dim friend5Class As String
    Dim friend5SEN As String
Dim friend6Class As String
    Dim friend6SEN As String
Dim friend7Class As String
    Dim friend7SEN As String
Dim friend8Class As String
    Dim friend8SEN As String
Dim friend9Class As String
    Dim friend9SEN As String
Dim friend10Class As String
    Dim friend10SEN As String

    Dim yyocCount As Integer
    Dim nnocCount As Integer
    Dim ynocCount As Integer
    Dim nyocCount As Integer
    Dim yyscCount As Integer
    Dim nnscCount As Integer
    Dim ynscCount As Integer
    Dim nyscCount As Integer
    Dim scOnlyCount As Integer
    Dim ocOnlyCount As Integer

    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your actual sheet name
    
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    
    ' Loop through each row
    For Each egoRow In ws.Range("A2:A" & lastRow)
        yyocCount = 0 ' Reset count for each ego
        nnocCount = 0 
        ynocCount = 0
        nyocCount = 0
        yyscCount = 0
        nnscCount = 0
        ynscCount = 0
        nyscCount = 0
        scOnlyCount = 0
        ocOnlyCount = 0
        
        ' Get ego's class and SEN
        egoClass = ws.Cells(egoRow.Row, 14).Value ' Class column
        egoSEN = ws.Cells(egoRow.Row, 15).Value ' SEN column
        
Dim startRowFriend1 as Long
startRowFriend1 = 2

        ' Find all occurrences of ego in friend1 column
        Set friend1 = ws.Range("B2:B" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Loop through each occurrence of ego in friend1
        Do Until friend1 Is Nothing
            ' Get the row where ego is found in friend1
            Dim friend1RowNumber As Long
            friend1RowNumber = friend1.Row
            
            ' Get the class and SEN of friend1
            friend1Class = ws.Cells(friend1RowNumber, 14).Value ' Class value of friend1
            friend1SEN = ws.Cells(friend1RowNumber, 15).Value ' SEN value of friend1

            ' Compare ego's class and SEN with friend1's class and SEN
            If egoClass <> friend1Class And egoSEN = "Y" And friend1SEN = "Y" Then
                yyocCount = yyocCount + 1 ' Increment yyocCount if conditions met
            ElseIf egoClass <> friend1Class And egoSEN = "N" And friend1SEN = "N" Then
                nnocCount = nnocCount + 1 ' Increment nnocCount if conditions met
            ElseIf egoClass <> friend1Class And egoSEN = "N" And friend1SEN = "Y" Then
                ynocCount = ynocCount + 1 ' Increment ynocCount if conditions met
            ElseIf egoClass <> friend1Class And egoSEN = "Y" And friend1SEN = "N" Then
                nyocCount = nyocCount + 1 ' Increment nyocCount if conditions met
            ElseIf egoClass = friend1Class And egoSEN = "Y" And friend1SEN = "Y" Then
                yyscCount = yyscCount + 1 ' Increment yyscCount if conditions met
            ElseIf egoClass = friend1Class And egoSEN = "N" And friend1SEN = "N" Then
                nnscCount = nnscCount + 1 ' Increment nnscCount if conditions met
            ElseIf egoClass = friend1Class And egoSEN = "N" And friend1SEN = "Y" Then
                ynscCount = ynscCount + 1 ' Increment ynscCount if conditions met
            ElseIf egoClass = friend1Class And egoSEN = "Y" And friend1SEN = "N" Then
                nyscCount = nyscCount + 1 ' Increment nyscCount if conditions met
            ElseIf egoClass = friend1Class And (egoSEN = "" Or friend1SEN = "") Then
                scOnlyCount = scOnlyCount + 1 
            ElseIf egoClass <> friend1Class And (egoSEN = "" Or friend1SEN = "") Then
                ocOnlyCount = ocOnlyCount + 1 
            End If
            
            ' Look for next occurrence of ego in friend1 column
            Set friend1 = ws.Range("B" & startRowFriend1 + 1 & ":B" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        Loop

Dim startRowFriend2 as Long
startRowFriend2 = 2
        
        ' Find all occurrences of ego in friend2 column
        Set friend2 = ws.Range("C2:C" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Loop through each occurrence of ego in friend2
        Do Until friend2 Is Nothing
            ' Get the row where ego is found in friend2
            Dim friend2RowNumber As Long
            friend2RowNumber = friend2.Row
            
            ' Get the class and SEN of friend2
            friend2Class = ws.Cells(friend2RowNumber, 14).Value ' Class value of friend2
            friend2SEN = ws.Cells(friend2RowNumber, 15).Value ' SEN value of friend2

            ' Compare ego's class and SEN with friend2's class and SEN
            If egoClass <> friend2Class And egoSEN = "Y" And friend2SEN = "Y" Then
                yyocCount = yyocCount + 1 ' Increment yyocCount if conditions met
            ElseIf egoClass <> friend2Class And egoSEN = "N" And friend2SEN = "N" Then
                nnocCount = nnocCount + 1 ' Increment nnocCount if conditions met
            ElseIf egoClass <> friend2Class And egoSEN = "N" And friend2SEN = "Y" Then
                ynocCount = ynocCount + 1 ' Increment ynocCount if conditions met
            ElseIf egoClass <> friend2Class And egoSEN = "Y" And friend2SEN = "N" Then
                nyocCount = nyocCount + 1 ' Increment nyocCount if conditions met
            ElseIf egoClass = friend2Class And egoSEN = "Y" And friend2SEN = "Y" Then
                yyscCount = yyscCount + 1 ' Increment yyscCount if conditions met
            ElseIf egoClass = friend2Class And egoSEN = "N" And friend2SEN = "N" Then
                nnscCount = nnscCount + 1 ' Increment nnscCount if conditions met
            ElseIf egoClass = friend2Class And egoSEN = "N" And friend2SEN = "Y" Then
                ynscCount = ynscCount + 1 ' Increment ynscCount if conditions met
            ElseIf egoClass = friend2Class And egoSEN = "Y" And friend2SEN = "N" Then
                nyscCount = nyscCount + 1 ' Increment nyscCount if conditions met
            ElseIf egoClass = friend2Class And (egoSEN = "" Or friend2SEN = "") Then
                scOnlyCount = scOnlyCount + 1 
            ElseIf egoClass <> friend2Class And (egoSEN = "" Or friend2SEN = "") Then
                ocOnlyCount = ocOnlyCount + 1 
            End If
            
            ' Look for next occurrence of ego in friend2 column
            Set friend2 = ws.Range("C" & startRowFriend2 + 1 & ":C" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        Loop

.
.
.
.
.
Dim startRowFriend8 as Long
startRowFriend8 = 2

    ' Find all occurrences of ego in friend8 column
        Set friend8 = ws.Range("I2:I" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Loop through each occurrence of ego in friend8
        Do Until friend8 Is Nothing
            ' Get the row where ego is found in friend8
            Dim friend8RowNumber As Long
            friend8RowNumber = friend8.Row
            
            ' Get the class and SEN of friend8
            friend8Class = ws.Cells(friend8RowNumber, 14).Value ' Class value of friend8
            friend8SEN = ws.Cells(friend8RowNumber, 15).Value ' SEN value of friend8

            ' Compare ego's class and SEN with friend8's class and SEN
            If egoClass <> friend8Class And egoSEN = "Y" And friend8SEN = "Y" Then
                yyocCount = yyocCount + 1 ' Increment yyocCount if conditions met
            ElseIf egoClass <> friend8Class And egoSEN = "N" And friend8SEN = "N" Then
                nnocCount = nnocCount + 1 ' Increment nnocCount if conditions met
            ElseIf egoClass <> friend8Class And egoSEN = "N" And friend8SEN = "Y" Then
                ynocCount = ynocCount + 1 ' Increment ynocCount if conditions met
            ElseIf egoClass <> friend8Class And egoSEN = "Y" And friend8SEN = "N" Then
                nyocCount = nyocCount + 1 ' Increment nyocCount if conditions met
            ElseIf egoClass = friend8Class And egoSEN = "Y" And friend8SEN = "Y" Then
                yyscCount = yyscCount + 1 ' Increment yyscCount if conditions met
            ElseIf egoClass = friend8Class And egoSEN = "N" And friend8SEN = "N" Then
                nnscCount = nnscCount + 1 ' Increment nnscCount if conditions met
            ElseIf egoClass = friend8Class And egoSEN = "N" And friend8SEN = "Y" Then
                ynscCount = ynscCount + 1 ' Increment ynscCount if conditions met
            ElseIf egoClass = friend8Class And egoSEN = "Y" And friend8SEN = "N" Then
                nyscCount = nyscCount + 1 ' Increment nyscCount if conditions met
            ElseIf egoClass = friend8Class And (egoSEN = "" Or friend8SEN = "") Then
                scOnlyCount = scOnlyCount + 1 
            ElseIf egoClass <> friend8Class And (egoSEN = "" Or friend8SEN = "") Then
                ocOnlyCount = ocOnlyCount + 1 
            End If
            
            ' Look for next occurrence of ego in friend8 column
            Set friend8 = ws.Range("I" & startRowFriend8 + 1 & ":I" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        Loop

Dim startRowFriend9 as Long
startRowFriend9 = 2

    ' Find all occurrences of ego in friend9 column
        Set friend9 = ws.Range("J2:J" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Loop through each occurrence of ego in friend9
        Do Until friend9 Is Nothing
            ' Get the row where ego is found in friend9
            Dim friend9RowNumber As Long
            friend9RowNumber = friend9.Row
            
            ' Get the class and SEN of friend9
            friend9Class = ws.Cells(friend9RowNumber, 14).Value ' Class value of friend9
            friend9SEN = ws.Cells(friend9RowNumber, 15).Value ' SEN value of friend9

            ' Compare ego's class and SEN with friend9's class and SEN
            If egoClass <> friend9Class And egoSEN = "Y" And friend9SEN = "Y" Then
                yyocCount = yyocCount + 1 ' Increment yyocCount if conditions met
            ElseIf egoClass <> friend9Class And egoSEN = "N" And friend9SEN = "N" Then
                nnocCount = nnocCount + 1 ' Increment nnocCount if conditions met
            ElseIf egoClass <> friend9Class And egoSEN = "N" And friend9SEN = "Y" Then
                ynocCount = ynocCount + 1 ' Increment ynocCount if conditions met
            ElseIf egoClass <> friend9Class And egoSEN = "Y" And friend9SEN = "N" Then
                nyocCount = nyocCount + 1 ' Increment nyocCount if conditions met
            ElseIf egoClass = friend9Class And egoSEN = "Y" And friend9SEN = "Y" Then
                yyscCount = yyscCount + 1 ' Increment yyscCount if conditions met
            ElseIf egoClass = friend9Class And egoSEN = "N" And friend9SEN = "N" Then
                nnscCount = nnscCount + 1 ' Increment nnscCount if conditions met
            ElseIf egoClass = friend9Class And egoSEN = "N" And friend9SEN = "Y" Then
                ynscCount = ynscCount + 1 ' Increment ynscCount if conditions met
            ElseIf egoClass = friend9Class And egoSEN = "Y" And friend9SEN = "N" Then
                nyscCount = nyscCount + 1 ' Increment nyscCount if conditions met
            ElseIf egoClass = friend9Class And (egoSEN = "" Or friend9SEN = "") Then
                scOnlyCount = scOnlyCount + 1 
            ElseIf egoClass <> friend9Class And (egoSEN = "" Or friend9SEN = "") Then
                ocOnlyCount = ocOnlyCount + 1 
            End If
            
            ' Look for next occurrence of ego in friend9 column
            Set friend9 = ws.Range("J" & startRowFriend9 + 1 & ":J" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        Loop

Dim startRowFriend10 as Long
startRowFriend10 = 2
    ' Find all occurrences of ego in friend10 column
        Set friend10 = ws.Range("K2:K" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        
        ' Loop through each occurrence of ego in friend10
        Do Until friend10 Is Nothing
            ' Get the row where ego is found in friend10
            Dim friend10RowNumber As Long
            friend10RowNumber = friend10.Row
            
            ' Get the class and SEN of friend10
            friend10Class = ws.Cells(friend10RowNumber, 14).Value ' Class value of friend10
            friend10SEN = ws.Cells(friend10RowNumber, 15).Value ' SEN value of friend10

            ' Compare ego's class and SEN with friend10's class and SEN
            If egoClass <> friend10Class And egoSEN = "Y" And friend10SEN = "Y" Then
                yyocCount = yyocCount + 1 ' Increment yyocCount if conditions met
            ElseIf egoClass <> friend10Class And egoSEN = "N" And friend10SEN = "N" Then
                nnocCount = nnocCount + 1 ' Increment nnocCount if conditions met
            ElseIf egoClass <> friend10Class And egoSEN = "N" And friend10SEN = "Y" Then
                ynocCount = ynocCount + 1 ' Increment ynocCount if conditions met
            ElseIf egoClass <> friend10Class And egoSEN = "Y" And friend10SEN = "N" Then
                nyocCount = nyocCount + 1 ' Increment nyocCount if conditions met
            ElseIf egoClass = friend10Class And egoSEN = "Y" And friend10SEN = "Y" Then
                yyscCount = yyscCount + 1 ' Increment yyscCount if conditions met
            ElseIf egoClass = friend10Class And egoSEN = "N" And friend10SEN = "N" Then
                nnscCount = nnscCount + 1 ' Increment nnscCount if conditions met
            ElseIf egoClass = friend10Class And egoSEN = "N" And friend10SEN = "Y" Then
                ynscCount = ynscCount + 1 ' Increment ynscCount if conditions met
            ElseIf egoClass = friend10Class And egoSEN = "Y" And friend10SEN = "N" Then
                nyscCount = nyscCount + 1 ' Increment nyscCount if conditions met
            ElseIf egoClass = friend10Class And (egoSEN = "" Or friend10SEN = "") Then
                scOnlyCount = scOnlyCount + 1 
            ElseIf egoClass <> friend10Class And (egoSEN = "" Or friend10SEN = "") Then
                ocOnlyCount = ocOnlyCount + 1 
            End If
            
            ' Look for next occurrence of ego in friend10 column
            Set friend10 = ws.Range("K" & startRowFriend10 + 1 & ":K" & lastRow).Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
        Loop

        
        ' Put Count under respective headers in the same row as ego
        ws.Cells(egoRow.Row, 17).Value = yyscCount
        ws.Cells(egoRow.Row, 18).Value = nyscCount
        ws.Cells(egoRow.Row, 19).Value = nnscCount
        ws.Cells(egoRow.Row, 20).Value = ynscCount
        ws.Cells(egoRow.Row, 21).Value = yyocCount
        ws.Cells(egoRow.Row, 22).Value = nyocCount
        ws.Cells(egoRow.Row, 23).Value = nnocCount
        ws.Cells(egoRow.Row, 24).Value = ynocCount
        ws.Cells(egoRow.Row, 26).Value = scOnlyCount
        ws.Cells(egoRow.Row, 27).Value = ocOnlyCount
    Next egoRow
End Sub

However, the output seems to have a miscount of some cells, usually short of 1 count. What I was expecting: for each ego, the number of students who nominated them as close friends are reflected in columns Q to X based on their attributes vis-a-vis the ego(class and SEN).

Here is my data layout:

Column A: student ID (ego)
Columns B to K: the nominations of their close friends (up to 10)
Column M: student ID (ego)-- used for VLookUp
Column N: Class
Column O: SEN
Columns Q to X: output count

I have spent 3 days mulling over this and would appreciate any help to see this through.

sample data (simplified):

expected output:

Corrected Data:

Ego	Friend1	Friend2	Ego	Class	SEN	NY_SC	YY_OC	YN_OC	NY_OC	ocOnly
S1	S2	S3	S1	1A	Y	0	1	0	1	0
S2	S3	S1	S2	1B	N	0	0	1	0	0
S3	S1	S4	S3	1B	Y	1	1	0	0	0
S4			S4	1A		0	0	0	0	1

Solution

Use FindNext to look for next occurrence of ego
Use For loop through 10 friends cols
Replace If clauses with Select Case

Microsoft documentation:

Range.Offset property (Excel)

Range.FindNext method (Excel)

Option Explicit
Sub CompareData3()
    Dim ws As Worksheet
    Dim lastRow As Long, iRow As Long
    Dim egoRow As Range
    Dim friendRng As Range
    Dim egoClass As String
    Dim egoSEN As String
    Dim friendClass As String
    Dim friendSEN As String
    Dim yyocCount As Long
    Dim nnocCount As Long
    Dim ynocCount As Long
    Dim nyocCount As Long
    Dim yyscCount As Long
    Dim nnscCount As Long
    Dim ynscCount As Long
    Dim nyscCount As Long
    Dim scOnlyCount As Long
    Dim ocOnlyCount As Long
    ' Set the worksheet
    Set ws = ThisWorkbook.Sheets("Validate") ' Change "Sheet1" to your actual sheet name
    ' Find the last row of data
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    ' Loop through each row
    For Each egoRow In ws.Range("A2:A" & lastRow)
        yyocCount = 0 ' Reset count for each ego
        nnocCount = 0
        ynocCount = 0
        nyocCount = 0
        yyscCount = 0
        nnscCount = 0
        ynscCount = 0
        nyscCount = 0
        scOnlyCount = 0
        ocOnlyCount = 0
        ' Get ego's class and SEN
        iRow = egoRow.Row
        egoClass = ws.Cells(iRow, 14).Value ' Class column
        egoSEN = ws.Cells(iRow, 15).Value ' SEN column
        ' Find all occurrences of ego in friend column
        Dim colRng As Range, sFirst As String, iCol As Long, sKey as String
        For iCol = 1 To 10
            Set friendRng = Nothing
            Set colRng = ws.Range("B2:B" & lastRow).Offset(, iCol - 1)
            Set friendRng = colRng.Find(egoRow.Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not friendRng Is Nothing Then
                sFirst = friendRng.Address
                ' Loop through each occurrence of ego in friend
                Do
                    ' Get the class and SEN of friend
                    friendClass = ws.Cells(friendRng.Row, 14).Value ' Class value of friend
                    friendSEN = ws.Cells(friendRng.Row, 15).Value ' SEN value of friend
                    ' Compare ego's class and SEN with friend's class and SEN
                    sKey = friendSEN & egoSEN
                    If egoClass = friendClass Then
                        Select Case sKey
                        Case "YY"
                            yyscCount = yyscCount + 1
                        Case "YN"
                            ynscCount = ynscCount + 1
                        Case "NN"
                            nnscCount = nnscCount + 1
                        Case "NY"
                            nyscCount = nyscCount + 1
                        Case Else
                            If Len(sKey) < 2 Then _
                            scOnlyCount = scOnlyCount + 1
                        End Select
                    Else
                        Select Case sKey
                        Case "YY"
                            yyocCount = yyocCount + 1
                        Case "YN"
                            ynocCount = ynocCount + 1
                        Case "NN"
                            nnocCount = nnocCount + 1
                        Case "NY"
                            nyocCount = nyocCount + 1
                        Case Else
                            If Len(sKey) < 2 Then _
                            ocOnlyCount = ocOnlyCount + 1
                        End Select
                    End If
                    ' Look for next occurrence of ego in friend column
                    Set friendRng = colRng.FindNext(friendRng)
                Loop While Not friendRng Is Nothing And sFirst <> friendRng.Address
            End If
        Next
        ' Put Count under respective headers in the same row as ego
        ws.Cells(iRow, 17).Value = yyscCount
        ws.Cells(iRow, 18).Value = nyscCount
        ws.Cells(iRow, 19).Value = nnscCount
        ws.Cells(iRow, 20).Value = ynscCount
        ws.Cells(iRow, 21).Value = yyocCount
        ws.Cells(iRow, 22).Value = nyocCount
        ws.Cells(iRow, 23).Value = nnocCount
        ws.Cells(iRow, 24).Value = ynocCount
        ws.Cells(iRow, 26).Value = scOnlyCount
        ws.Cells(iRow, 27).Value = ocOnlyCount
    Next egoRow
End Sub

Ego	Friend1	Friend2	Ego	Class	SEN	NY_SC	YY_OC	YN_OC	NY_OC	ocOnly
S1	S2	S3	S1	1A	Y	0	1	0	1	0
S2	S3	S1	S2	1B	N	0	0	1	0	0
S3	S1	S4	S3	1B	Y	1	1	0	0	0
S4			S4	1A		0	0	0	0	1

Ego	Friend1	Friend2	Ego	Class	SEN	NY_SC	YY_OC	YN_OC	NY_OC	ocOnly
S1	S2	S3	S1	1A	Y	0	1	0	1	0
S2	S3	S1	S2	1B	N	0	0	1	0	0
S3	S1	S4	S3	1B	Y	1	1	0	0	0
S4			S4	1A		0	0	0	0	1

Ego	Friend1	Friend2	Ego	Class	SEN	NY_SC	YY_OC	YN_OC	NY_OC	ocOnly
S1	S2	S3	S1	1A	Y	0	1	0	1	0
S2	S3	S1	S2	1B	N	0	0	1	0	0
S3	S1	S4	S3	1B	Y	1	1	0	0	0
S4			S4	1A		0	0	0	0	1