VBA Code to check Cell Value in another sheet, before copy match record to Sheet

Hi there,

Im currently working on a Simple Inventory file managing records of Machines that come in and out of my store.

I have created a Form - InStore form, whereby i receive Machines and I input this details into the form and the record is saved inside my InStore Table.
I have successfully coded this with some useful tips provided by helpful members on Excel Forum.

Currently I have a Deploy Form, whereby I will Search for a Machine using a Serial Number. I have a button Event for this.

I need some help with a bit of complex coding where by when Perform a Search using Serial Number in my Deploy Form, it will check if this value is found inside "InStore" Sheet, Column(A:A).
If record is not found, a Pop-Up Box to appear to show the error and clear the cell. It its found the Cell will not be cleared.

Upon Clicking on Submit i will need to copy the data from the Row (A:N) from Sheet(InStore) into my "Deploy" Sheet as the last record. The Row that was copied from "InStore" Sheet will be deleted as the data is blank.

Appreciate your assistance on this.

Please Login or Register  to view this content.

I have tried your code that you have provided and modified it to reference my sheets.

Please Login or Register  to view this content.

So when i execute this Code, i first try with a Non-existent serial number or with an Existing serial number i get an error saying Object Required.

Apologies Bakerman2,

I have tried the update code on my Form, when i click on the button this time it does not do any action.
I do not receive a Msgbox nor does it clear the "C6" contents.

Hi Bakerman2,

I have tried this code instead for the Search and manage to get the Search Function work.

Please Login or Register  to view this content.

Please Login or Register  to view this content.

I have manage to figure out on how to do the copying. However for this code, when im copying these range of cells to another sheet. its copyings the formula references that are found inside(A1:G1).
How do i amend this code to include to save just Values only?

ws3.Range("A1:G1").Copy ws2.Range("O" & lr2)