Return corresponding values of one search criteria using VBA

[rakotonirinas]

Hello,
I'm having a difficulty to resolve what I will consider a simple VBA problem.
In one worksheet ("Find result"), I have a cell ("B2") called member iD which consists of the column A of the data on another worksheet ("Membership").
The purpose of the exercise is to search if the cell ("B2") is on the column A of ("Membership") worksheet and list all the corresponding results (Date and Product) on the column ("D" & "E") of the ("Find result") worksheet. [as per the example attached]
So I assume the VBA code will search first the corresponding value and then copy all matching criteria and then paste along the row until there is no more. [working as a loop]

I have started the following code but because the result needs to be going to another worksheet, i.e. ("Find result"), I don't get anything.

Please Login or Register  to view this content.

Clearly it is not perfect and something is missing but cannot figure it out. I would appreciate your suggestion, help or advice.

Thank you

[Arkadi]

Hi rakotonirinas,

A few notes about the code you posted.

1. Does it run through and just output nothing? It seems to me that you should encounter a problem because "Member ID" should not work as a variable... since it has a space in it... maybe add an underscore? (Member_ID).
2. Your code starts by clearing B2... (Sheets("Find result").Range("B2").ClearContents), but then next line then makes Member_ID the value of that cell... so isn't Member_ID blank at that stage?
3. The code pastes in D6, so every match would over-write the previous one.
4. No workbook was actually attached... to attach a workbook scroll down and on the right you will find "Go Advanced" - click that, then scroll down and find "Manage Attachments", you will have a screen where you can select a file, and then upload it.

[rakotonirinas]

Hi Arkadi,

Thank you for your help.

I've deleted the part of clearing the content of the cell B2. So what should be the formula that would list all possible result as per the attachment for instance?

I've attached the spreadsheet for more explanation.

Many thanks again.

[Arkadi]

One of your problems is also that the active sheet is Find Result, and when you copy the ranges without specifying the source sheet, it assumes the activesheet.

See if this works for you (I also changed copy to just assigning values, so that the formatting would stay the way you format your output sheet):

Please Login or Register  to view this content.

[rakotonirinas]

Arkadi. Thank you very much. It works like a charm and it looks so simple after you tweaked it. Much appreciated.

[rakotonirinas]

For the formula provided by Arkadi above, is there any possibility to Auto-Wrap the cell where the result will go, i.e., in Find result sheet From Range E6 to E20. My current issue is that when the value from the Membership worksheet is pasted, I have to wrap manually to be able to see the whole content of some results. I would like to auto-wrap or auto-fit if it is possible so the whole content of the result will be directly visible.

Thank you.

Please Login or Register  to view this content.

[Arkadi]

My pleasure, glad I could help Thanks for the rep and marking the thread as solved!

[Arkadi]

Please Login or Register  to view this content.

[rakotonirinas]

Once again, thank you Arkadi. I cannot thank you enough.
Clearly I would never have guessed to write it this way

Please Login or Register  to view this content.

Muchas gracias!