VBA Macro Includes a VLookUp

Hello...I am trying to create a VBA Macro that seems pretty simple but I cannot get it right.

Column A in Worksheet 1 contains a list of numeric values that will change daily; the user will paste a new list into column A.

I want to add a VLook Up in the macro that will read the occupied cell in column A and if there is a match bring the answer back to the adjacent cell in column B. If there is no match, I want to bring back text of my choosing rather than the default #N/A. The Table Array will be on a seperate sheet in the same workbook and this data will never change.

If the cell in column A is blank then I want the adjacent cell in column B to be blank; this will only occur at the end of the list in column A...there should never be blank cells anywhere between the first and last lines of data. So essentially, once we have reached the bottom of column A, the macro is done.

If anyone can help me with this I would be very appreciative.

Thank you.

It needs to be a macro because this is going to be given to other users who will be pasting the new data in daily...I want to create a simple process for them.

Originally Posted by ChemistB

Does this need to be a macro or can you use formulas in Column B?

It needs to be a macro because this is going to be given to other users who will be pasting the new data in daily...I want to create a simple process for them.