Posts by Greeb1979

    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.

    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.

    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.

    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?

    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: