Checking Data and replcing when an error found.

[Ortz]

Hi Everyone

I hope someone can help me here I have had some excellent help in the past.

I have a workbook that I have to use at work daily, we use Excel 2003 with Windows 7. I have attached a stripped down version here, it records data that is input daily by one of our admin clerks. The proper workbook has 12 sheets, one for each month of the year. There is also an extra sheet, FED, that is used to check individual entries for correct start dates entered on the monthly sheets in columns G, this is important for funding we receive.

The information in the Fed sheet is copied and pasted from another system monthly and is to check specific months for the correct start dates.

At the moment I have to physically check these entries and change the dates in the monthly sheet with the date in the FED sheet if they are different, this takes hours of my time as there are often up to 1000 entries a month

Is there a way that this can be done with VBA very quickly ? I have no knowledge of VBA but have been told that something called Lookup would be able to do this. Basically the number in Column B of the FED sheet needs to be checked to see if its present in column B of the monthly sheet, if it finds that number in column B of the monthly sheet it would check the date in column G of the month sheet and if different to that of the date in column C of the FED sheet it should change it to the FED sheet date. It should be able to do this selecting individual monthly sheets and not checking every sheet.

Can anyone help me with this please ? It would be awesome and save me so much time but I have no knowledge of VBA at all.

Many thanks in advance

Ortz

[dilipandey]

Hi Ortz,

See column P in Feb sheet in attached workbook. Thanks.

Regards,
DILIPandey

<click on below 'star' if this helps>

[Ortz]

WoW Thats quick and fantastic dilipandey

Is there a way that the revised date now in Column P can automatically replace the incorrect date in Column G ?

Thanks

Ortz

[JBeaucaire]

This macro will do that for you. For each code on the FED sheet it will:

1) determine which sheet the code SHOULD be on
2) If the code is on that sheet already, simply update the DATE field
3) If the code is NOT on that sheet, search all other sheets to find it and when found, copy it to the correct sheet and update the DATE
4) If the code is NOT found on any sheet, a new entry is added to the bottom of the correct sheet
5) If the needed sheet does not exist, the macro will create it

For #5, I put the "headers" in row1 of the FED sheet for reference.

Please Login or Register  to view this content.

[Ortz]

Thank You JBeaucaire

Thats perfect

Ortz

[dilipandey]

Hi Ortz,

Yes... there could be a way - with the help of a macro we can achieve this, where the revised dates of Column P can automatically replace the incorrect date in Column G but I just saw that Mr.Jerry has already done the task. Cheers

Regards,
DILIPandey

<click on below 'star' if this helps>

[JBeaucaire]

When you run the macro on your "real" sheet, what happens?

[Ortz]

Hi JBeaucaire

Apologies for the late reply. The macro works perfectly, does exactly what you coded it to do which is great.

The only problem is when the command button is pressed if there is no data in the 2 columns it causes a runtime error. I know people are not supposed to but they do and keep on doing it Is there a way that if the command button is pressed with no data a message box comes up asking for data to be input to stop the runtime error ?

Hope you can help with this

Thanks

Ortz

[JBeaucaire]

Please Login or Register  to view this content.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

[Ortz]

Thanks JBeaucaire

Perfect

Many Thanks

Ortz