+ Reply to Thread
Results 1 to 11 of 11

macro to split data to respective columns not working as expected

  1. #1
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,045

    macro to split data to respective columns not working as expected

    I have column A which has a set of data which needs to be split to their respective headers column B /C D.

    Row1 represents the headers.

    In applying 3 different macros with same output,not giving expected results

    1 first giving compile error variable not defined.

    Please Login or Register  to view this content.


    the second macro giving invalid procedure callor argument rin time error 5

    Results(R, 2) = Trim(Mid(Data(R, 1), 9, Len(Data(R, 1)) - 16))

    Please Login or Register  to view this content.
    3 rd macro split data incorrectly


    Please Login or Register  to view this content.
    Attached Files Attached Files

  2. #2
    Forum Expert JLGWhiz's Avatar
    Join Date
    02-20-2011
    Location
    Florida, USA
    MS-Off Ver
    Windows 10, Excel 2013
    Posts
    2,030

    Re: macro to split data to respective columns not working as expected

    sub Split() works for me without txt being declared, But it is good practice to declare all variables with a Dim statement.
    Sub SplitTariffData() ran without error for me.
    Sub SplitIt() ran withou error for me.

    I think the problem is that one or more of the cells in column A has less that the number of characters needed to execute the Mid statement. You can fix that with and If...Then statement
    Please Login or Register  to view this content.
    You might want to throw an Else in the If...Then statement to give it a default value.
    Any code provided by me should be tested on a copy or a mock up of your original data before applying it to the original. Some events in VBA cannot be reversed with the undo facility in Excel. If your original post is satisfied, please mark the thread as "Solved". To upload a file, see the banner at top of this page.
    Just when I think I am smart, I learn something new!

  3. #3
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,389

    Re: macro to split data to respective columns not working as expected

    Cell C2: =LEFT(A2,8)
    Cell D2: =MID(A2,10,FIND("|",SUBSTITUTE(A2," ","|",LEN(A2)-LEN(SUBSTITUTE(A2," ",""))))-10)
    Cell E2: =RIGHT(A2,2)

    Copy the formulas down.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  4. #4
    Forum Expert sintek's Avatar
    Join Date
    12-04-2015
    Location
    Cape Town
    MS-Off Ver
    2013 | 2016 | 2019
    Posts
    8,634

    Re: macro to split data to respective columns not working as expected

    Please Login or Register  to view this content.
    Good Luck
    I don't presume to know what I am doing, however, just like you, I too started somewhere.....
    One-day, One-problem at a time!!!
    If you feel I have helped, please click on the star to left of post [Add Reputation]
    Also....add a comment if you like!!!!
    And remember......Mark Thread as Solved.
    Excel Forum Rocks!!!

  5. #5
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,045

    Re: macro to split data to respective columns not working as expected

    Quote Originally Posted by JLGWhiz View Post
    sub Split() works for me without txt being declared, But it is good practice to declare all variables with a Dim statement.
    Sub SplitTariffData() ran without error for me.
    Sub SplitIt() ran withou error for me.

    I think the problem is that one or more of the cells in column A has less that the number of characters needed to execute the Mid statement. You can fix that with and If...Then statement
    Please Login or Register  to view this content.
    You might want to throw an Else in the If...Then statement to give it a default value.
    I have tried for as it is and for three as per screenshots ,two first code got run time error and the code gave me inconsistent data.
    Attached Images Attached Images

  6. #6
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,045

    Re: macro to split data to respective columns not working as expected

    Mumps and Sintek thanks for solution provided as it works both formula and vba code.

    But my approach is much more to vba so that users d' ont mess up with formula as it is a robust database of 35000 rows .

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs
    MS-Off Ver
    MS Office 2019; O365
    Posts
    18,973

    Re: macro to split data to respective columns not working as expected

    Late to this party. With Power Query, this can be accomplished with 4 easy steps.

    Here is the Mcode

    Please Login or Register  to view this content.
    Steps explained:

    1. Load your table to PQ
    2. Split the column based upon the first space on the left
    3. In the split column, split it based upon the first space on the right
    4. Close and Load to your excel spreadsheet.

    time spent to do this is approximately 30 seconds.

    If additional data is added to the original source file, it is only necessary to refresh the query. No need to rerun or reprogram as you would have to using VBA.

    For more understanding of PQ, the book M is for (Data) Monkey is a good choice.

  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,389

    Re: macro to split data to respective columns not working as expected

    Try:
    Please Login or Register  to view this content.

  9. #9
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,378

    Re: macro to split data to respective columns not working as expected

    Try the attached
    Please Login or Register  to view this content.
    Attached Files Attached Files

  10. #10
    Forum Contributor
    Join Date
    02-11-2014
    Location
    mauritius
    MS-Off Ver
    EXCEL 2016
    Posts
    1,045

    Re: macro to split data to respective columns not working as expected

    Thank you Jindon , Mumps1,alansidman,sintek, JLGWhiz for assistance

    All works marvelously.

  11. #11
    Forum Guru
    Join Date
    08-15-2004
    Location
    Tokyo, Japan
    MS-Off Ver
    2013
    Posts
    17,378

    Re: macro to split data to respective columns not working as expected

    You are welcome and thanks for the rep.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] VBA to split respective multiline from 3 columns
    By chilli16 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-30-2018, 11:14 AM
  2. [SOLVED] macro split specific data to respective columns but should not exceed 32 characters
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-01-2018, 02:24 PM
  3. Formula to split data to their respective columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 7
    Last Post: 01-06-2018, 06:42 AM
  4. [SOLVED] vba split data in respective columns based on unique list sheet
    By JEAN1972 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-29-2017, 02:40 PM
  5. [SOLVED] formula to split data in respective columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-06-2017, 02:21 PM
  6. [SOLVED] formula to split data to their respective columns
    By JEAN1972 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-16-2016, 11:50 AM
  7. [SOLVED] Autofitting of columns not working as expected
    By Ezzard in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 03-12-2015, 07:37 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1