Code stops working correctly halfway through executing

Question

I am trying to gather data about player stats from one spreadsheet sheet, perform a calculation on that data, and then paste that data into a cell on another sheet if the names match.

Its a bit tricky as the sheet with the stats data has less records than my other sheet, and the names are the shortened versions rather than the full names.

I have been using the Range.Find function to check if the names of the players on the stats sheet are within the names cells of the first sheet, and if so paste the formula.

My approach so far is to: 1. search the long list to see if the stats sheet name exists in the long set of names 2. if it exists, check to see if the name in the long list corresponding to the row of the current cell is the same as the name in the stats sheet, and if not, move to the next cell in the long list and check again. 3. repeat until I have the location of the cell which matches the name of the stats sheet cell, and fill in the formula in a cell on the same row as the name on the long list.

My code is (it starts to say all the names are not in the list even though they are):

'
' PlayerImpact Macro
'

'
Dim rng As Range, cell As Range, PSrng As Range, player As Range, namerng As Range, FDaddress As Range, playercheck As Range
'Dim namek As String
Dim i As Long, j As Long, k As Long
Dim a As Single, b As Single, c As Single, d As Single, tot As Single
Set rng = Sheet1.Range("P2:P542")
Set namerng = Sheet1.Range("A2:A542")
Set PSrng = Sheet6.Range("A2:S390")
k = 1
i = 2
j = 2
'For j = 1 To rng.Rows.Count
Do While j < rng.Rows.Count
    Debug.Print " "
    Debug.Print "i: " & " " & i
    Debug.Print "j: " & " " & j
    Debug.Print "k: " & " " & k
    Debug.Print "Filtered Data Player:" & " " & namerng(k)
    Debug.Print "Stats Player:" & " " & PSrng.Cells(i, 3).Value
    Set cell = rng(j)
    Set player = namerng.Find(PSrng.Cells(i, 3).Value, LookIn:=xlValues, Lookat:=xlPart)
    If Not player Is Nothing Then
            Debug.Print "namerange loop: " & " " & player.Value
            Debug.Print "namerange k in loop: " & " " & namerng(k)
            Debug.Print "Stats Player in loop:" & " " & PSrng.Cells(i, 3).Value
            Set playercheck = namerng(k).Find(PSrng.Cells(i, 3).Value, LookIn:=xlValues, Lookat:=xlPart)
            If Not playercheck Is Nothing Then
                j = k
                'cell.Formula = "=('Player Stats Value'!G" & (i + 1) & "-'Player Stats Value'!$G$2)/'Player Stats Value'!$G$2+('Player Stats Value'!I" & (i + 1) & "-'Player Stats Value'!$I$2)/'Player Stats Value'!$I$2+('Player Stats Value'!J" & (i + 1) & "-'Player Stats Value'!$J$2)/(2*'Player Stats Value'!$J$2)+('Player Stats Value'!K" & (i + 1) & "-'Player Stats Value'!$K$2)/(2*'Player Stats Value'!$K$2)+('Player Stats Value'!Q" & (i + 1) & "-'Player Stats Value'!$Q$2)"
                i = i + 1
                k = k + 1
                j = j + 1
                Debug.Print "In List, Player Check Match: " & " " & player.Value
                Debug.Print "Playercheck Value: " & " " & playercheck.Value
            Else
                Debug.Print "In List, No match Player Check:" & " " & player.Value
                k = k + 1
                If k > namerng.Rows.Count Then
                    k = 1
                End If
            End If
    Else
      Debug.Print "Not in List: " & " " & PSrng.Cells(i, 3).Value
      i = i + 1
      'cell.Value = 0
      j = j + 1
      'k = k + 1
    End If
Loop

the two spreadsheets can be seen here

A sample of the output when it starts to go wrong:

i:  13
j:  14
k:  14
Filtered Data Player: Ademola Lookman
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Ademola Lookman
Stats Player in loop: Adrián
In List, No match Player Check: Adrián San Miguel del Castillo

i:  13
j:  14
k:  15
Filtered Data Player: Adrian Mariappa
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Adrian Mariappa
Stats Player in loop: Adrián
In List, No match Player Check: Adrián San Miguel del Castillo

i:  13
j:  14
k:  16
Filtered Data Player: Adrián San Miguel del Castillo
Stats Player: Adrián
namerange loop:  Adrián San Miguel del Castillo
namerange k in loop:  Adrián San Miguel del Castillo
Stats Player in loop: Adrián
In List, Player Check Match:  Adrián San Miguel del Castillo
Playercheck Value:  Adrián San Miguel del Castillo

i:  14
j:  17
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Adrian Mariappa
Not in List:  Adrian Mariappa

i:  15
j:  18
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Ahmed Elmohamady
Not in List:  Ahmed Elmohamady

i:  16
j:  19
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Ainsley Maitland-Niles
Not in List:  Ainsley Maitland-Niles

i:  17
j:  20
k:  17
Filtered Data Player: Ahmed El Mohamady
Stats Player: Alex Iwobi
Not in List:  Alex Iwobi

Answer 1

From what I understand about your problem, this code should do everything you're looking for:

Sub PlayerImpact()

Dim rng As Range, namerng As Range, PSrng As Range
Dim lngCol As Long

Set rng = Sheet1.Range("P2:P542")
Set namerng = Sheet1.Range("A2:A542")
Set PSrng = Sheet6.Range("C3:C390")

For j = 1 To namerng.Rows.Count

    If Not PSrng.Find(namerng(j).Value2, LookIn:=xlValues, Lookat:=xlPart) Is Nothing Then
        lngRow = PSrng.Find(namerng(j).Value2, LookIn:=xlValues, Lookat:=xlPart).Row

        rng(j).Formula = "=('Player Stats Value'!G" & lngRow & "-'Player Stats Value'!$G$2)/" & _
        "'Player Stats Value'!$G$2+('Player Stats Value'!I" & lngRow & "-'Player Stats Value'!$I$2) /" & _
        "'Player Stats Value'!$I$2+('Player Stats Value'!J" & lngRow & "-'Player Stats Value'!$J$2)/" & _
        "(2*'Player Stats Value'!$J$2)+('Player Stats Value'!K" & lngRow & "-'Player Stats Value'!$K$2)/" & _
        "(2*'Player Stats Value'!$K$2)+('Player Stats Value'!Q" & lngRow & "-'Player Stats Value'!$Q$2)"
    Else
        rng(j).Value2 = 0 'alternative: rng(j).Value2 = ""
    End If

Next

End Sub