Search record in table and if not found then enter new record any VBA code?

[shezz]

Hi experts i have a query please anybody can solve it..

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Name[/TD]
[TD]Amount[/TD]
[TD]Descp[/TD]
[TD][/TD]
[TD]Search ID here >>[/TD]
[TD]1004[/TD]
[/TR]
[TR]
[TD]1001[/TD]
[TD]ABC[/TD]
[TD]500[/TD]
[TD]On Cash[/TD]
[TD][/TD]
[TD]ID[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]1002[/TD]
[TD]XYZ[/TD]
[TD]1000[/TD]
[TD]Credit[/TD]
[TD][/TD]
[TD]Name[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]1003[/TD]
[TD]ZYW[/TD]
[TD]1000[/TD]
[TD]On Cash[/TD]
[TD][/TD]
[TD]Amount[/TD]
[TD]#N/A[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]Descp[/TD]
[TD]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

In blue words is Sheet1 which is a form in black words is data table in sheet2, i want on sheet1 when i enter a ID the related data displayed through vlookup function but when there is no data as mentioned above e.g ID 1004 which is not available in data sheet now i want when there is no matching record i want to enter new record in sheet1 in form not in data table but the problem comes when i enter my formula is being erased so for next record i have to enter formula in all cells, i want a VBA code to done this automaticaly, i mean code reads when there is matching record in data table it must be displayed but if its not then i can enter new record through form. anybody can solve please. thanks in advance

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Sheet1 and click 'View Code'. Paste the macro into the empty code window that opens up. Close the code window to return to your sheet. Enter an ID cell B1 and exit the cell. If the ID exists in Sheet2, the data will be copied over automatically. If the ID does not exist in Sheet2, continue enter the data in B2 to B5. When you exit B5, that data will be copied to Sheet2.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("B1,B5")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim ID As Range
    On Error GoTo errHandler
    Select Case Target.Row
        Case Is = 1
            Set ID = Sheets("Sheet2").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            If Not ID Is Nothing Then
                ID.Resize(1, 4).Copy
                Range("B2").PasteSpecial Transpose:=True
                Application.CutCopyMode = False
            End If
        Case Is = 5
            Range("B2:B5").Copy
            Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial Transpose:=True
            Application.CutCopyMode = False
    End Select
errHandler:
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub