Problem to to find match within multiple lines within Cell

[stockish]

Hi Forumers,

I have this little problem to get my head around fixing the issue to find match an entry in one column and search for the duplicate entry in another column in a different sheet.

Result that Im trying to achieve here is to copy the whole row of a match entry in Sheet2. If it is comparing a single entry(sheet1) with another cell(sheet2) also with single entry, no issue found and I managed to copy the row to a new sheet(sheet3). But I the compare the cell that have multiple lines within a cell, no output will be copied to new sheet(sheet3) even though the cell contain match entry.

Below are the macro I used for this purpose:

Please Login or Register  to view this content.

[Leith Ross]

Hello stockish,

You will generally get better and more reliable results using Copy and PasteSpecial across worksheets. This also allows you more choices in what you wish to paste.

Her is the macro with this option added...

Please Login or Register  to view this content.

[stockish]

Hi Leith,

Apology for the incorrect format when posting the codes in this forum, will ensure to follow rules in the future before posting.

Thank you very much for assistance in improving my macro to achieve better result, much appreciated but I still cant get the intended output based on my macro. Really appreciate if you or anyone can shed some lights to resolve my issue.

For example :

Sheet1A5 contain value of '5' but it failed to find the match in Sheet2 even though Sheet2B2 has that value.

[Leith Ross]

Hello stockish,

You are comparing the values as identical. If you don't have an identical match where sheet1's cell contents exactly match sheet2's cell contents, nothing will be copied.

You need to do a partial match of the cell's contents. If it is true then copy the cell. I would like to help you further but it is 2:40 AM my time and I can barely read the screen. Hopefully, someone else will help you with the coding.

[stockish]

Hello stockish,

You are comparing the values as identical. If you don't have an identical match where sheet1's cell contents exactly match sheet2's cell contents, nothing will be copied.

You need to do a partial match of the cell's contents. If it is true then copy the cell. I would like to help you further but it is 2:40 AM my time and I can barely read the screen. Hopefully, someone else will help you with the coding.

I totally get your point to do a partially match of the cell contents but to do a macro based on that is something that I am not able to translate in vba language due my level knowledge in vba. Trying to improve my vba skill from time to time though but this time around i really hit a wall..

Thank you anyway Leith for your help..get some good rest

[Leith Ross]

Hello stockish,

Looks like nobody stepped in to help. I modified the macro to perform a case insensitive partial match using the InStr method. The macro now produces eleven matches.

Please Login or Register  to view this content.