Match data on source and destination sheet and change value

I have extracted data from multiple sheets and now want to mark this data as "Paid" and for the macro to go back and change the value in the original sheet on a row in column "AR" to "PAID.
All the columns have the same data type in.

The extracted data contains the original sheet name and each row has a unique ID No to it.

I know I probably need to use INDEX and MATCH and then Offset to post value.

Attached worksheet shows the data and columns.

Thanks
DK

Hi Holger

Sorry for the delay in responding to your reply.
I've looked at it and think I understand what it should be doing. There are a couple of changes in my version now where COL Sheet will = "D" and Col ID will = "E".
Also my first "Paid" line now starts at "W6". I have made these changes to your code.
When I run it or step through the code nothing seems to happen at all.

DK

Hi Holger

I've attached a new sheet that shows SCH6 which contains the original data.
I have a macro that extracts this to the AP sheet as shown.

Your code was to work backwards and but the text PAID in COL AR of the original data row. I tried using PAID and this
still did not work.

What I really need is instead of putting PAID against the line in the AP sheet is to put a date - say 24-Mar-14
and for this ddate to then be put in COL AR of the original data row so that this can be used to work with a cashflow sheet.

Hope you can help as this is final part of my jigsaw.

Thanks for looking at.

DK.

Hi Holger

Thanks for replying. I did have the correct D/E/I etc on the version that I tried and was using PAID in W6 but it was not finding anything in the rngFound.

Could you explain what the code below does - I'm concerned when I see Worksheets.Add?

Thanks
DK


If Not Evaluate("ISREF('" & .Cells(rngCell.Row, cstrCOL_SHEET).Value & "'!A1)") Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = .Cells(rngCell.Row, cstrCOL_SHEET).Value

Hi Holger
Sorry about the tags - still finding my way around these forums.
I understand why you might want the code to check that a sheet exists but I definately don't want the worksheet created if it doesn't. That should never be the case. Thats not part of the function of this code.
Are you able to send back a working example based on my sheet as I still can't get it to register that there is a row in which column "W" has "PAID" in. Because of this the code stops.
Thanks
DK

Hi Holger
I'm travelling at the moment but tonight will send a new example that clearly shows and explains the data and actions needed.
Your code will has the worksheet.add in which I don't need.
The example will enable you to put your code in and see if it works.
If it does then it can be uploaded again and I can look at it.
Thank you for your efforts and patience.
DK

Hi Holger

I've attached a sample working sheet. The source data is in SCH5 and the extracted data is in AP.
I've written in blue txt would has happened / how the sheets operate and what the code needs to be able to do.
I've also written in AP what in the best of worlds I would like the code to be able to do.

Hope you can help

Many thanks
DK

Hi Holger

When I state that I have another macro - I mean that the original source data is in SCH. I run an "Extract" Macro and it takes the data from Sch5 and puts it in Sheet AP. This all works well.
The formula in E12-E24 was to create a unique id number for the line of information. In hindsight and as per your suggestion this would not be needed if the row number was captured and brought over to Sheet AP instead and then this could be used to go backwards and revise the ".PAID" update.


My code and formula structures are probably not elegant as have only been dealing with vba for a few months and getting the result outweighs my knowledge of clean code.

I am totally open to suggestions and am daily on a steep learning curve - so very happy for you to revise and provide a better solution

Kind regards
David

Hi Holger

This didn't work at first but then I remembered your comments about the text fields so I changed them to general and it seemed to like it and is now working. Thank you.

Two more things - As I mentioned previously what I ideally need is instead of "PAID" in COLW - that the user enters a real date and that that date for each line is taken back and put into SCH5 for instance.
If this is too difficult then it would work using just 1 single date that would be found in "W4". Is this possible?

For the original code you have provided you mentioned how the sheet and row no could be put into "AP" at the time of extraction. This would mean that the code that I have for this would need to be altered. This code is below - are you able to amend so that it does this?
Not sure if I've done the code tags right?

Please Login or Register  to view this content.