VBA to copy & Paste Adjacent cell when value found

[shiva_reshs]

Hello,

Below code helps me to search sheet1 value in sheet2 and when value found past it in Data GV sheet, I need a tweak here, where in searched data in sheet2 is copied back in sheet1's adjacent cell.

1) Copy Sheet2's cell D and E and paste it back in Cell O & P of sheet1

Please Login or Register  to view this content.

Thanks!!
Shiva

[AB33]

It is not clear whether you want to copy columns C and D of sheet 2 in to O and P of sheet 1 as column P of sheet has value, or you want copy them on the same sheet.
Just clarify which columns of which sheet should be pasted in to which sheet and column.

[shiva_reshs]

I will move the P column of sheet2 to right which would make P column "blank".

I need VBA to copy C & D of Sheet2 to O & P of Sheet1.

Thanks you so much for your help!

Regards

[shiva_reshs]

My given code searches N2 of Sheet1 thru the value of column C in Sheet2 and when it find "Yes" under column G for found Value in C it copies the entire row of sheet1 and paste it in sheet3(Data GV).

but instead of copying the sheet1, I want adjacent cell value of C column of sheet2 ( Cell D and F) to be copied and pasted back in Sheet1 Adjacent value of N2.
E.g. if N2 of Sheet1 value is found under C6 of Sheet2 ( Provided it meets the condition of "Yes" in same row of G column) it copies the D6 and E6 and copy back to O2 & P2 of Sheet1.

Hope this help.

Thanks a bunch!!

Regards,

[shiva_reshs]

116 views, no reply yet. Strange!!! I thought this would be easy code..!
AM still trying to fix the code not luck yet.

Thanks

[AB33]

I have not ignored you, I was off-site.

Please Login or Register  to view this content.

[shiva_reshs]

Thanks AB.

Screenflickers, but nothing gets copied or pasted.

Regards,
Shiva

[AB33]

Shiva,
You only have two rows with UA01(Rows 3 and 4) and column G has to be also yes. If one of the condition is not met, the code will return false.

[shiva_reshs]

Hi,

I am not sure what is going wrong here but I am not able to execute your code. For me, I just get screenflicker but not else.
I have attached the spreadsheet with your code added in it.

FYI. I use MS Excel 2010. I had the similar issue once when I used 2003 code in Excel 2010. I see you have been using 2010 so not sure what is wrong.

[AB33]

I have now added another code, so you have two working codes.

[shiva_reshs]

Your this code works like a charm.!! Thank you so much for your assistance.
One tweak request. I wanted the same code in another my project but your code is tricky for me to tweak to my preference. I never understood the concept of X & Y even on my school

Here what I have in another spreadsheet.
1) Instead of column C of lookup search in Sheet2, I would like to replace lookup search to column B (Sheet2).
2) Instead of Column G (Yes) in sheet2, this time I want Column C "No"
3) Keep the C & D as it is, as I will be copying cell C & D from sheet2
4) Data to be pasted in P & Q of Sheet1 ( Earlier it was O & P )

your code below for easy reference.

Please Login or Register  to view this content.

Thank you so much for your help. You are the best!

Regards
Shiva

[shiva_reshs]

Note: Sheet2 has 30,000 rows data. When I tried to execute your code I received error message "Run Time Error '7' Out of Memory" on below line

Please Login or Register  to view this content.

[AB33]

Use this line, if this does not work, I need to see your large sample.

Please Login or Register  to view this content.

[shiva_reshs]

Great. It do not give me Out of memory error now.

Now I get "Run time error '1004': Application - defined or object - defined error on below code.

Please Login or Register  to view this content.

However, I still need your help in changing the cell values.
Here what I have in another spreadsheet.
1) Instead of column C of lookup search in Sheet2, I would like to replace lookup search to column B (Sheet2).
2) Instead of Column G (Yes) in sheet2, this time I want Column C "No"
3) Keep the C & D as it is, as I will be copying cell C & D from sheet2
4) Data to be pasted in P & Q of Sheet1 ( Earlier it was O & P )

Thanks

[AB33]

Please attach your new sample.

[shiva_reshs]

Sorry for the delay. Had issue in uploading the file.

[AB33]

Try the attached.

[shiva_reshs]

hey AB,

It works, but in this spreadsheet I need pick value to be "No". Your code works as long as it stays "Yes" but when I change it to "No" in your code, I get run time error on below line

Please Login or Register  to view this content.

Advice please.

Thanks

[AB33]

It means the array returns 0 as there is no row which has no.
Change the last line in to

Please Login or Register  to view this content.

[shiva_reshs]

changed, I do not get error but nothing happens.

[AB33]

That was exactly what I said in my last post.
The conditions of No and match value of L column return 0. For the code to copy both conditions ought to be right, not just one.
If you want one condition to be true, change the AND to OR.

[shiva_reshs]

cool. Per your instruction I changed the code

Please Login or Register  to view this content.

works like a charm.. Thank you so much for your help.

Regards
Shiva