Match/Copy/Paste Formula

[Jeannie2006]

Hello All, I was hoping someone could give me the formula for a task at hand? From my last two posts I gave up and started all over again and used the Record Macro. I got to the point to where I have no choice but to do a manual formula and well with no experience in VBA I was hoping I could talk a Guru into helping me.

Here is what I need to do.

I need to match data from sheet3 column E to data in sheet1 column B and if there is a match I need to copy that row of data from sheet3 column F thru column K and paste that information into sheet1 column H thru column N.

Sounds simple enough, right...

Thank you to anyone who is willing to take on my request.

[Fotis1991]

We can do this using formulas. Do you want to do this only using VBA?

[Jeannie2006]

Yes, please I would like to do this using only VBA only. There is a reason for my madness.

[Fotis1991]

Every one has a reason for doing anything..

Good luck.

[Jeannie2006]

Thank you, I am going to need all the luck I can get.

[AB33]

Jennie,
Please attach your sample!

[Jeannie2006]

I hope I get this right, never downloaded a file.

[AB33]

Jeannie,
You have 3 sheets. 1358(sheet1),2237(sheet2), 889D(sheet3). Do you want to match column B of 1358 and 2237,or column E of sheet3? You do not have any data from column F on wards, except the heading. Please clarify which columns and sheet needs to be matched and if there is a match in which sheet and which columns need to be copied to?

[AB33]

Try the attached

[Jeannie2006]

Okay here we go:

On the 1358 spreadsheet I need to match Column B to data in spreadsheet 889D Column E. If there is a match I need Column E to copy and paste into Column H on the 1358 page. Then if there is a match I need to copy corresponding rows F thru K from 889D into the 1358 spreadsheet columns I thru N.

On the 2237 spreadsheet I need to match Column B to data in spreadsheet 889D Column E. If there is a match I need Column E to copy and paste into Column I on the 2237 page. Then if there is a match I need to copy corresponding rows F thru K from 889D into the 1358 spreadsheet columns J thru O.

The data changes from month to month and I never know when data will be in each column/row.

Does this help?

Thank you for helping me with this, I really appreciate it.

[Jeannie2006]

I have to leave for work now, I will try this soon as I get home. Thank you!

[AB33]

Adjusted as per your request

[Jeannie2006]

Thank you so much for your hard work on this, but it is not working properly. Each row should have a dollar amount in one of the columns. If you compare the matching numbers (which looks to be working great) there is a corresponding $$ within the row. Look at the 889D and you will see every cell in column E has a value attached to it in one of the columns.

I hate to ask but could you look at it again

[AB33]

Jeannie,
I have lost you!
Which $$ amounts you are referring to? All columns with dollar amount in sheet889 have been copied in to the other two sheets if there is a match as you can see on the attached. Have you tried the last attached? Not every row will be copied, only the matched columns, otherwise, it is a not match code

[Jeannie2006]

Let me work on the example page and re-send it. This way you can compare what you had sent me verses the end result. Is that okay?

[AB33]

Jeannie,,
Have you tried post 12 and if so, tell me what is missing? You want to copy from F-K(All days) if there is a match. Okay, the numbers are not formatted as a currency, but this is just a formatting issue.

[Jeannie2006]

Attached is what the final product should look like. If you notice in column H and I everyone has a matching number to Column B on the 1358 and 2237 spreadsheets so there should be a $$ in one of the proceeding columns. Please let me know if this helped. I am truely greatful for all you are doing for me.

[Jeannie2006]

Sorry I messed up on my reply. I am such a computer idiot sometimes.

Okay, If you review the second spreadsheet you sent me you can see you had matches. It pulled the information into some of the columns but not all of them. I have attached the spreadsheet again with the results I am hoping to have.

Again thank you for all you are doing for me.

[AB33]

On the attached, there are the original sheets(Your desired result) and the new sheets with amended code, so that you can compare them side by side. If anything is missing, let me know and will correct it.

[Jeannie2006]

Wow, that looks great! With this macro what normally took about 14 hours to do will now only take 15 minutes. This is so amazing, thank you so much for your help!

I tried to incorporate it in with my origninal macro and I get Variable not Defined on the following:

X (I put in Dim X As Range, it seemed to be okay)
dic (???)
NR (????)
i (????)

Any suggestions???

[AB33]

Jeannie,
You are right to include the variable as I may had an "Option Explicit" on my original code. If you have this on the top of your code, you need to include all variables used in the code.


NR&,i&,dic
with the dim section

Or remove the option explicit and does not bother about defining variables(Though not recommended)

[Jeannie2006]

I don't know what to do with these if I shouldn't remove the option explicit?

Sorry I don't understand, Please fill in the blanks for me.

Dim dic As
Dim NR As
Dim i As
Dim X As

[Jeannie2006]

I tried to take out the Option Explicit and now my original macro will not run and if I leave it there the macro you wrote for me will not work, it just keeps highlighting stuff and giving me error messages. I am at such a lose.

[AB33]

I now have checked the attached I sent you and there is no option explicit on the code. You can check it for yourself. I suspect the reason you can't run your code is because you may have set up declaring your variables on your option in VBA.
Here is the revised top code bit with option explicit.

Option Explicit

Please Login or Register  to view this content.

[Jeannie2006]

You are amazing!!!! It worked like a charm. I can't say thank you enough for all your work in this matter. I will definately let my boss know you helped me, even if your name is AB33... Thank you for being there for people like me. I guess I must say fairwell and let you attend to the next needy soul.

Keep your fingers crossed that when I use this at work everything actually works.

Thank you,

[AB33]

Jeannie,
You are welcome!

[Jeannie2006]

Well it didn't work at work. No error's though. I am on the right track so I will plug away at it.

Thank you,