Modifying Existing Code to Check if the entry exists

Hi There,

I need your help,

I have a code below that works well with a userform that adds my data from my userform to an excel worksheet. It works great and all, but id like to modify it to have it check and see if the file number (fn) exists before writting it back to the worksheet, if it exists, then msgbox "Error: Record Cannot be added - (Duplicate Entry)" and do nothing else, if the file number does not exist, proceed as normal and execute the rest of the code.

With Me 
    Select Case .h9.Value 
    Case "ACTIVE" 
        Set ws = Worksheets("Active_Data") 
        iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
         
        ws.Cells(iRow, 1) = .h1 
        ws.Cells(iRow, 2).Value = .h2 
         
        ws.Cells(iRow, 3).Value = fn 
        Me.h3.Text = fn 
         
        ws.Cells(iRow, 4).Value = .h4 
        ws.Cells(iRow, 5).Value = .h5 
        ws.Cells(iRow, 6).Value = .h6 
         
        If notify.Value = "True" Then 
            ws.Cells(iRow, 7).Value = "Yes" 
        Else 
            ws.Cells(iRow, 7).Value = "No" 
        End If 
         
        If remind.Value = "True" Then 
            ws.Cells(iRow, 8).Value = "Yes" 
        Else 
            ws.Cells(iRow, 8).Value = "No" 
        End If 
         
        ws.Cells(iRow, 9).Value = .h7 
        ws.Cells(iRow, 10).Value = .h8 
        ws.Cells(iRow, 11).Value = .h9 
        ws.Cells(iRow, 12).Value = .h10 
        ws.Cells(iRow, 13).Value = .h11 
        ws.Cells(iRow, 14).Value = .h12 
        ws.Cells(iRow, 15).Value = .h13 
         
        ws.Cells(iRow, 16).Value = Now 
        Me.h14.Value = Now 
         
        MsgBox "The record has been sucessfully added!" _ 
        , vbInformation + vbOKOnly, "Record added" 
         
    Case "INACTIVE" 
        Set ws = Worksheets("Inactive_Data") 
        iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row 
        ws.Cells(iRow, 1) = .h1 
        ws.Cells(iRow, 2).Value = .h2 
         
        ws.Cells(iRow, 3).Value = fn 
        Me.h3.Text = fn 
         
        ws.Cells(iRow, 4).Value = .h4 
        ws.Cells(iRow, 5).Value = .h5 
        ws.Cells(iRow, 6).Value = .h6 
         
        If notify.Value = "True" Then 
            ws.Cells(iRow, 7).Value = "Yes" 
        Else 
            ws.Cells(iRow, 7).Value = "No" 
        End If 
         
        If remind.Value = "True" Then 
            ws.Cells(iRow, 8).Value = "Yes" 
        Else 
            ws.Cells(iRow, 8).Value = "No" 
        End If 
         
        ws.Cells(iRow, 9).Value = .h7 
        ws.Cells(iRow, 10).Value = .h8 
        ws.Cells(iRow, 11).Value = .h9 
        ws.Cells(iRow, 12).Value = .h10 
        ws.Cells(iRow, 13).Value = .h11 
        ws.Cells(iRow, 14).Value = .h12 
        ws.Cells(iRow, 15).Value = .h13 
         
        ws.Cells(iRow, 16).Value = Now 
        Me.h14.Value = Now 
         
        MsgBox "The record has been sucessfully added!" _ 
        , vbInformation + vbOKOnly, "Record added" 
         
    End Select 
End With

Any help with this is greatly appreciated.

Much thanks for all your help in advance.

Thanks,

Jay

Couldn't you just run an If statement like:

If Range("YourRange").Value = fn Then
    MsgBox "Error:Record Cannot be added - (Duplicate Entry)" 
    Exit Sub
End If

Mayhap I am over simplifiing the matter.

As Mordred suggests you can compare the variable to the cell before populating it.

You can also reduce the duplication in your code by simply assigning the reference to the correct worksheet before transferring values.

I have assumed if the value already exists then no information is transferred

With Me
    
    Set ws = Nothing
    
    Select Case .h9.Value
    Case "ACTIVE"
        Set ws = Worksheets("Active_Data")
    Case "INACTIVE"
        Set ws = Worksheets("Inactive_Data")
    End Select

    If ws Is Nothing Then
        ' unexpected value for h9
    Else
        iRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
         
        If ws.Cells(iRow, 3).Value = fn Then
            MsgBox "Cell already set"
        Else
            ws.Cells(iRow, 1) = .h1
            ws.Cells(iRow, 2).Value = .h2

            ws.Cells(iRow, 3).Value = fn
            Me.h3.Text = fn
             
            ws.Cells(iRow, 4).Value = .h4
            ws.Cells(iRow, 5).Value = .h5
            ws.Cells(iRow, 6).Value = .h6
             
            If notify.Value = "True" Then
                ws.Cells(iRow, 7).Value = "Yes"
            Else
                ws.Cells(iRow, 7).Value = "No"
            End If
             
            If remind.Value = "True" Then
                ws.Cells(iRow, 8).Value = "Yes"
            Else
                ws.Cells(iRow, 8).Value = "No"
            End If
             
            ws.Cells(iRow, 9).Value = .h7
            ws.Cells(iRow, 10).Value = .h8
            ws.Cells(iRow, 11).Value = .h9
            ws.Cells(iRow, 12).Value = .h10
            ws.Cells(iRow, 13).Value = .h11
            ws.Cells(iRow, 14).Value = .h12
            ws.Cells(iRow, 15).Value = .h13
             
            ws.Cells(iRow, 16).Value = Now
            Me.h14.Value = Now
             
            MsgBox "The record has been sucessfully added!" _
            , vbInformation + vbOKOnly, "Record added"
        End If
    End If
End With