Posts by jstar88

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    sry to bother u guys again but after running this macro on a new project, I came across another error saying "Subscript out of Range" and when debugging it, it points to the following line:


    for PCI:

    Code
    DEST(II, J) = ORG(I, J)


    and for jindon:

    Code
    b(n, II) = a(I, II)


    What does this error exactly mean? not sure why it is not working. I think the macro keeps on stopping when an entry like "C1364" is in the place column.

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    thank you PCI so much! works amazing and just what i need.
    sry for taking so much of your time.



    shout outs to jindon and PCI for being the most dedicated and hardworking, working to solve all of my problems for sure.
    thanks again! hopefully, no more problem with this.

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    well, i don't need the final_treat part anymore. i found out that we only need to stop after the hyphen treat and the format of the Oz09 after using comma and then the hyphen is the format that i need.


    IN OTHER WORDS, is it possible we can get the "data" sheet in the attachment to match the "Target" sheet after i run the COMMA_HYPHEN TREAT macro without running the FINAL_TREAT?


    Thanks PCI for your dedication and help![ATTACH=CONFIG]39696[/ATTACH]

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    so, before the final treatment, i need the order from Oz11 to match with Oz09.


    Well, i'm currently not using the final treatment so i'm doing the comma treat and then the hyphen treat so i need the order that comes out from the Oz09. thanks!

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    hey PCI,


    thanks for the update. i can tell u that the COMMA_HYPHEN treat runs faster but it doesn't give me the same result as before (i think the order of the result doesn't match).
    you may want to compare the results of the macro with the comma and hyphen treat separated and this one together.


    in addition, when i update the HYPHEN_TREAT macro it works much better as well but it is still slowed down from first running the COMMA_TREAT followed by the HYPHEN_TREAT.


    thanks! would love to know why the COMMA_HYPHEN treat result doesn't match

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    thank you so much jindon!!! i appreciate it so much and can't express how much you have helped me.
    i have one little question though...how do i change:

    Code
    a = Sheets("data").Range("a1").CurrentRegion.Value


    so that it reads the name of whatever sheet that i am using because not every sheet that i want to run the macro on is titled "data". can i just use it on the active worksheet?

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    for some reason, it still doesn't match. i updated the attachment to include something more similar to the files i am working on. although the number of rows match, the entries in each row do not.
    You can compare the first 20 rows of the "Result" sheet which is from running your macro and the "Target" sheet which is what is needed.


    Thanks for your help!
    [ATTACH=CONFIG]39626[/ATTACH]

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    well if you run the macro that PCI had made within the Oz9 attachment, it does everything that I need. I just wanted to find a way to get the macro to run faster as my excel spreadsheet has 400+ rows of data.

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    Hi Jindon,


    Thanks for joining in. Basically, if you open PCI's Attached Oz9 file, I am trying to get each entry in the "Place Column" on separate rows by separating the hyphens and commas. For instance,
    [TABLE="width: 124"]

    [tr]


    [TD="class: xl65, width: 124"]U17,U120,C3-C5 becomes
    [/TD]

    [/tr]


    [/TABLE]
    U17
    U120
    C3
    C4
    C5


    and
    U12A_49-U12A_52 becomes
    U12A_49
    U12A_50
    U12A_51
    U12A_52

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    yeah due to limitations...i don't think i can send any data. basically, it's the same as the file you sent except that each row has about 20 commas in the "Place" column and there are about 100+ rows of data.
    hope you understand

  • Comma delimited list and rows into columns

    Re: Comma delimited list and rows into columns


    hey PCI, everything is working perfectly with the macro except I would really appreciate it if you could help me with one change (hopefully minor).


    i have to run the macro in a demo next week and even after shortening my spreadsheet from 400+ rows of data to 100, running the macro kind of freezes excel or it takes quite a while to finish. is there a way to optimize the code?