Excel VBA - adding data to worksheet where unique identifier accumulates

I had a mate of mine create a VBA script for me for a rather large excel file containing football player game records.

The file currently has around 7000 player names over 190000 odd rows and each player is granted a unique PID (PlayerID). So a player with 10 games played will have 10 rows with one unique PID.

The script goes off to a website, copies player data and pastes it to the end of my excel worksheet called (Goals). When this data is added to the worksheet if a player matched on surname and firstname already exists, the PID will be populated with that players PID. If, in the data being added from the website, the player does not already exist a new unique number is to be given to this player.

For example: The Player Fred SMITH PID=1234 already exists, any new record for him will receive the PID of 1234. A new player Joe BLOGGS is added via the script the PID for him shall be the highest existing PID + 1. So if Fred SMITH has the highest PID then Joe BLOGGS would then be assigned the PID of 1235.

The script works well until a new player is added.. Data before import PID | surname | firstname | Game | 1233| Jones | Mark | 1 1234| Smith | Fred | 2

Expected after import - Joe Blogs New player PID | surname | firstname | Game | 1233| Jones | Mark | 1 1234| Smith | Fred | 2 1235| Bloggs| Joe | 3 1234| Smith | Fred | 3

Actual after import - Joe Blogs New player PID | surname | firstname | Game | 1233| Jones | Mark | 1 1234| Smith | Fred | 2 1235| Bloggs| Joe | 3 1236| Smith | Fred | 3

I can see why this happens as the script says add 1 in column A, but how can I change it so it adds 1 to the highest number in column A rather than the number on the row above?

Here's the script For d = 1 To 300000

                    If Worksheets("Goals").Range("G" & CStr(d)).Value = surname Then

                        If Worksheets("Goals").Range("H" & CStr(d)).Value = firstname Then

                            PID = Worksheets("Goals").Range("A" & CStr(d)).Value
                            ID = Worksheets("Goals").Range("B" & CStr(d - 1)).Value + 1

                            Exit For

                            Else:

                            If Worksheets("Goals").Range("H" & CStr(d)).Value = "" Then

                                    PID = Worksheets("Goals").Range("A" & CStr(d - 1)).Value + 1
                                    ID = Worksheets("Goals").Range("B" & CStr(d - 1)).Value + 1

                                Exit For

                            End If

                        End If

                    Else:

                            If Worksheets("Goals").Range("A" & CStr(d)).Value = "" Then

                                    PID = Worksheets("Goals").Range("A" & CStr(d - 1)).Value + 1
                                    ID = Worksheets("Goals").Range("B" & CStr(d - 1)).Value + 1

                                Exit For

                            End If

                    End If

                Next d