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

shezz

New Member
Joined
Mar 19, 2015
Messages
9
Hi experts i have a query please anybody can solve it..

ID
NameAmountDescpSearch ID here >>1004
1001ABC500On CashID#N/A
1002XYZ1000CreditName#N/A
1003ZYW1000On CashAmount#N/A





Descp#N/A

<tbody>
</tbody>

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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,118
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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,597
Messages
5,597,089
Members
414,122
Latest member
eazyyexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top