A macro that will search a column for a value and copy the value to another sheet.

Hello,

I'm new to creating macros and I've been playing around trying to make one for hours without any luck.

I have two sheets on my Spreadsheet. "Start" and "Register"

What I need the macro to do is as follows -


Search column B on sheet "Register" for a value that is entered into cell U7 on sheet "Start"

If no value is found then a message displays 'No value found'

If the value is exactly the same as the value entered on U7, then the macro copies the value on sheet "start" in U8. And copies that value into column "P" in the exact same row as the value "U7" that was found on sheet "Register"


So this is how I dream it will work -

On the "Start" sheet I enter a serial number in U7 for example 001, and a date returned (e.g. 29/05/15) in cell U8. I then click the update button which is hopefully linked to the above macro.

When I click that button the macro searches column B on sheet 'register' for serial number 001. When 001 is found the macro will copy the date from cell U8 on sheet 'start' (29/05/15) and copy that date into column "P" on sheet 'Register' in the exact same column as 001 was found. (So if 001 was found on sheet 'Register' in cell B12, the macro will copy the date in U8 on sheet 'Start' into cell P12 on sheet 'Register'.

If someone could help me on the above issue you would be a lifesaver!

As we have 1000s of serialised items than are lent out it would save days going through records if a macro could find and populate the entered return date.

I hope the above makes sense.

Regards,

Jack


This has been cross posted here -

http://www.mrexcel.com/forum/excel-q...ml#post4170481

Not answer has yet been received, I will indicate in this post if an answer has been submitted on the other forum.

What you need is simplicity itself.

A sheet specific macro on "Start" sheet to monitor U7 and U8
If U7 is a number and U8 is a date
then the macro calls a subroutine

The subroutine searches column B on sheet 'register' for serial number from U7
If a match is found the macro saves the date from U8 into column P of the same row.

You will need 2 macros because the sheet specific macro cannot alter another sheet.

The sheet specific macro is:-

Please Login or Register  to view this content.

Right Click on the sheet name "Start" at the bottom of excel and select view code.

Paste this code in the window that opens and then close that window.


The subroutine needs to be placed in a normal macro module.

Select Visual Basic from the developer menu and then select insert module

Paste this code in the window that opens and then close that window.

You are then set to go.

Please Login or Register  to view this content.

It should have worked

But you need to make sure the sheet names are as you described in your original post.