VBA Userform to check file for records, count them and then write to same file

[bcassidy1986]

Afternoon,

I'm currently building a VBA user form that will be used for ticking off friend referrels from current customers.

Existing customers can refer up to 5 friends to us - so my userform firstly needs to check the old policy number against the list that has already been compiled and if this old policy number has not appeared 5 or more times write the detials entered to the same file it just checked.

So far my code looks like;

Private Sub CheckButton_Click()
    'set dimensions
        Dim FVWorkbook As Workbook
        Dim FVWorksheet As Worksheet
        Dim OldPolicyRange As Range
        Dim SearchFor As Long
        Dim OldCount As Long
        
    'check username is entered
        If Trim(Me.UserName.Value) = "" Then
            Me.UserName.SetFocus
            message = MsgBox("Please enter your user name", vbCritical, "Missing User Name")
            Exit Sub
        End If
        
    'check new policy number entered and correct length
        If Len(Me.NewPolicy.Text) <> 9 Then
            Me.NewPolicy.SetFocus
            message = MsgBox("New Policy number missing or not correct length", vbCritical, "New Policy Number Error")
            Exit Sub
        End If
    
    'check old policy number entered and correct length
        If Len(Me.OldPolicy.Text) <> 9 Then
            Me.OldPolicy.SetFocus
            message = MsgBox("Old Policy number missing or not correct length", vbCritical, "Old Policy Number Error")
            Exit Sub
        End If
        
    'set variables
        Set FVWorkbook = Workbooks.Open("S:\Loyalty Scheme.xlsx")
        Set FVWorksheet = FVWorkbook.Worksheets(1)
        Set OldPolicyRange = FVWorksheet.Range("A:A")
        SearchFor = OldPolicy.Value
    'if count is greater than zero show messege
        If Application.WorksheetFunction.CountIf(OldPolicyRange, SearchFor) >= 5 Then
            OldCount = Application.WorksheetFunction.CountIf(OldPolicyRange, SearchFor)
            FVWorkbook.Close False
            messege = MsgBox("Old Policy has referred " & OldCount & " Times, customer can not refer again" & vbCr & vbCr & "Check detials and click 'Clear Details' if needed", vbCritical, "Customer Referred Too Many Times")
        Else
        If Application.WorksheetFunction.CountIf(OldPolicyRange, SearchFor) > 0 Then
            OldCount = Application.WorksheetFunction.CountIf(OldPolicyRange, SearchFor)
            FVWorkbook.Close True
            messege = MsgBox("Old Policy has referred " & OldCount & " time(s) already not including this one")
        Else
            FVWorkbook.Close False
            messege = MsgBox("Policy number not in list")
        End If
        End If
            
End Sub

So the user form asks for the old policy number and the new policy number as well as a user name - it already checks that the policy number is the correct 9 digits and can then opens the log of already submitted records, makes sure the old policy number does not appear 5 or more times. If it does it flashes up a warning messege, if it doesnt i would then like it to write to the same file it just read...

And thats when i get stuck; i've tried using the open to append and even then asking it to find the last row add one and write directly to cell references but it's not working.

Hope you can help with this.

Cheers