fpl.xlsm

Sub CalculateFantasyPoints_PerMatch()
    Dim ws As Worksheet
    Dim lastRow As Long
    Dim i As Long, j As Long
    Dim pos As String
    Dim minutes As Variant
    Dim goals As Long, cleansheet As Long, concede As Long
    Dim yellow As Long, red As Long
    Dim totalPoints As Double
    Dim cellValue As String
    
    Set ws = ThisWorkbook.Sheets(2) ' change if sheet name differs
    lastRow = ws.Cells(ws.Rows.Count, "D").End(xlUp).Row
    
    ws.Range("T2").Value = "Fantasy Points"
    
    For i = 3 To lastRow
        pos = UCase(Trim(ws.Cells(i, "C").Value))
        goals = Val(ws.Cells(i, "O").Value)
        cleansheet = Val(ws.Cells(i, "P").Value)
        yellow = Val(ws.Cells(i, "R").Value)
        red = Val(ws.Cells(i, "S").Value)
        concede = Int(Val(ws.Cells(i, "Q").Value) / 2) * -1
        totalPoints = 0
        
        ' --- Check each gameweek column (E to M) ---
        For j = 5 To 13 ' columns E–M
            cellValue = Trim(ws.Cells(i, j).Value)
            If IsNumeric(cellValue) Then
                minutes = Val(cellValue)
                If minutes >= 60 Then
                    totalPoints = totalPoints + 2
                ElseIf minutes > 0 Then
                    totalPoints = totalPoints + 1
                End If
            ElseIf cellValue <> "" And cellValue <> "C" And cellValue <> "T" Then
                ' treat text values other than C or T as invalid
            End If
        Next j
        
        ' --- Goals ---
        Select Case True
            Case pos = "GK" Or pos = "DF"
                totalPoints = totalPoints + goals * 6
            Case pos = "MF"
                totalPoints = totalPoints + goals * 5
            Case pos = "FW"
                totalPoints = totalPoints + goals * 4
        End Select
        
        ' --- Clean sheets ---
        Select Case True
            Case pos = "GK" Or pos = "DF"
                totalPoints = totalPoints + cleansheet * 4
            Case pos = "MF"
                totalPoints = totalPoints + cleansheet * 1
        End Select
        
        ' --- Cards ---
        totalPoints = totalPoints + concede - yellow - (red * 3)
        
        ws.Cells(i, "T").Value = totalPoints
    Next i
    
    MsgBox "Fantasy points calculated successfully!", vbInformation
End Sub