VBA find and replace using array from table - Issues with multiline text

  • Hi All, I'm a relative beginner at VBA and have been grappling with this issue for a while now. I've tried various methods and feel like i'm finally getting somewhere, so any help would be greatly appreciated.

    I've been using the following code (Posted by TheSilkCode on another site I wont name), to find a part number from a table and replace it with the part number and description.

    The Code works fine where the description is a single line however when multiple lines of text are used I get Run-time error '13': Type mismatch.

    As the description is taken from another spreadsheet I don't control, I can't change the format of the source to be on a single line. So I'm looking for help to make the code work with multiline text.


    Example-

    If PN001 is replaced with

    PN001 ABC, DEF

    the code works fine..


    If PN001 is replaced with

    PN001:

    ABC

    DEF

    Then the Run-Time error'13' occurs


    Code:

  • Attach sample workbooks (your workbook and the description workbook)

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox, I can't upload the original file so I've created an example (Attached). After creating the example I found that multiple lines of text worked fine. So after much experimentation trying to discover what the issue is, it appears that it relates to the number of characters rather than multiple lines. It just happens that the multiple lines in the original document exceeded the number of characters, leading me down the wrong path.


    In the attached example on worksheet "Model" model no's are listed in column A, with part numbers listed in column E, I've added a few more columns shown as other info to make it more representative of the original document.


    On worksheet "Array Table" the part numbers are listed in column A, with the Part number and example description in column B.


    The Macro is activated by the button on the top left of the Model Worksheet shown as "Update Part Number".


    The Macro scans down the column A of the table array finds the part number in the Model Worksheet and replaces the part number with part number and description from Column B of the Array table.


    As the Macro reaches PN015 on the array table the Macro halts with Run-Time Error '13' Type Mismatch, I believe this is due to the number of characters.


    So I guess my question has changed to how can I make the Macro work with more characters?

  • Try the attached.


    I think the Find method you were using has a limit of 255 characters, the new code assigns all data to arrays and works with those arrays before writing the modified array x back to the worksheet and thus updating it.


    Code now assigned to the button:


    Note I have added an error trap and a message in the event of a description not being found for a part number in sheet 1.


    I have indicated in the code where you need to change the Column number if it is anything other than 5 (Column E) in your actual file.

    Files

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox, thanks so much for your help, I Haven't had chance to have a play around with it until today. Unfortunately I found when I add a new line or push the button again, multiple error messages pop up and it blanks must of the cells in the column. I understood enough of the original code to be able to resolve issues, but unfortunately your code is far beyond my understanding. If you have any suggestions i'd be grateful.

  • Attach the workbook where the errors occurred

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox, I'm using the new code example you sent, when I add a new line to the spreadsheet the first part number automatically updates without the button press, however any subsequent part numbers do not populate. If I press the button again then multiple error messages occur from the Msgbox in the code, after closing these error Messages the column is mostly left blank, on occasion adding a new line also seems to trigger the Macro, although I can't seem to get that to occur at the moment.

  • The errors occurred when the button was clicked again after adding new data because the code was trying to match Part Number + Description with Part Number in the Table on Array Table sheet.


    Try this, it will ignore Part Numbers that have already been updated with a Description.

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.

  • Hi KjBox, I've come across an issue when I use the code on the actual spreadsheet. The actual Part numbers can differ in length i.e PN001 PN123456 which I didn't show on my example.


    I believe the issue is on line 15


    " If Len(Trim(z(ii))) = 8 Then" - I changed the value to 8 to allow for the longer part numbers, however after doing this shorter part numbers in the same cell now get deleted.


    Is there a way to set this to a range i.e. between 4 and 8?


    Thanks again.

  • Try

    Code
    1. If Len(Trim(z(ii))) > 3 And Len(Trim(z(ii))) < 9 Then

    We now have a reputation system in place. If my reply helped please "Like" the reply by clicking the "Like" icon at bottom right of my reply.