1
501
1001
1501
2001
2
502
1002
1502
2002

Sub number()
Dim i As Long, oCN As Long, oColumn As String
oColumn = "A"

oCN = Columns(oColumn).Column
For i = 1 To 500
    Range(oColumn & Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = i
    Range(oColumn & Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = i + 500
    Range(oColumn & Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = i + 1000
    Range(oColumn & Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = i + 1500
    Range(oColumn & Cells(Rows.Count, oCN).End(xlUp).Offset(1, 0).Row).Value = i + 2000
Next i
End Sub

=500*MOD(ROW()-1,5)+INT((ROW()-1)/5)+1