Lookup and Copy Cells with more than 255-characters

[longbow007]

Hello, could someone please help me with the following Excel 2003 VBA problem:

In the attached workbook named: Part Number and Comments Lookup.xls --- there are 2 x worksheets named:

• Master_List
• SupplierComments

I need to look-up the Part Numbers in the Master_List worksheet to see if there is any corresponding Part Numbers in the SupplierComments worksheet.

If so, I need the corresponding Comments in column K (SupplierComments worksheet) to be copied over to the Master_List worksheet in column Q.

For example: Part Number 101 (located in cell C3 of the Master_List worksheet) has a corresponding part number in cell C3 of the SupplierComments worksheet.

The comments in cell K3 (SupplierComments worksheet) need to be copied across to cell Q3 (Master_List worksheet).

The main difficulty is that some of the cells in column K (SupplierComments worksheet) exceed 255 characters.

I was trying a VLOOKUP in cell Q2 (Master_List worksheet) with the following formula and dragging it down column Q, however the cells that contain over 255 characters were truncated:

=VLOOKUP(C2,SupplierComments!$C$2:$K$65,10,False)

If someone could please help me with some VBA code to do this --- it would be greatly appreciated.

Kind regards,

Chris

[jindon]

Isn't it 9?
=VLOOKUP(C2,SupplierComments!$C$2:$K$65,9,False)

working here.

[longbow007]

Hi jindon, my bad --- it is 9 --- sorry about that.

Cheers,

Chris

[jindon]

That formula is working properly with 2013.

If you still have problem, how about Index/Match?
=INDEX(SupplierComments!$C$2:$K$65,MATCH(C2,SupplierComments!$C$2:$C$65,0),9)

[longbow007]

Hi jindon, thank you for your reply - much appreciated. Unfortunately, the INDEX MATCH formula works does not work properly in Excel 2003 as it truncates cells with more than 255 characters.

As you advised previously, it does work with Excel 2010 and 2013.

Unfortunately, I need this to work in Excel 2003 because that is the version we use at work.

Cheers,

Chris

[FDibbins]

Can you break the data in the column (K?) into 2 parts, then bring each part in om it's own?

[jindon]

Then vba...
It updates each time you select "Master_List" sheet.
To Thisworkbook code module

Please Login or Register  to view this content.

To "Master_List" sheet code module

Please Login or Register  to view this content.

[longbow007]

Hi FDibbons, thank you for your reply - much appreciated.

I possibly could break the data in column K into 2 parts and then concatenate each of the 2 parts together in column Q of the Master_List worksheet.

I simply do not how to do this whilst performing a lookup?

Regards,

Chris

[longbow007]

Hi jindon, thank you so much for your hard work and efforts in providing me with the VBA code - it works well - very grateful

[FDibbins]

I simply do not how to do this whilst performing a lookup?

You would still do the lookup on the full cell, but just bring back the 2 components.

But it seems that Jonmo's code works for you