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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
11,718
Office Version
  1. 2013
  2. 2010
Platform
  1. Windows
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,509
Messages
5,832,163
Members
430,113
Latest member
CoeurDeLion

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