Modifying The Code to Check if the entry exists

[jason_kelly]

Hi There,

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 variable (fn) exists before writting it back to the worksheet, if it exists, 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 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 in advance,

Jay

[shrivallabha]

Hello,

I am also searching for an answer for a userform I've designed. But fortunately, it has got the code you need. You can modify it suit your case.

http://www.vbaexpress.com/forum/showthread.php?t=35548

Hope this helps.

[mikerickson]

By "if the file number variable (fn) exists" do you mean "is fn <> null".

Are you asking to distinguish between

"fn=0 because the user made it so"
and
"fn=0 because 0 is the null value for data type Long."

If its the case that the user would never set fn=0, testing for fn=0 would work.

OR, are you asking if the value fn is in a list of already existing file numbers?