VBA Macro to loop text to columns through all rows of data

[ejb52]

Hi,

I've been visiting this forum through Google search to help me with Excel questions for a while now, and it seems like the help is consistently timely and effective for other users' specific questions, so I went ahead and registered to post a question of my own.

I am trying to get a macro to run in excel that takes a simple text to columns command in one line of data and runs the command on a loop through however many rows of data there happen to be. I thought getting this to work would be more straightforward, but I've been trying unsuccessfully for a while now, which led me to seek help from the experts on this board.

I've attached two screenshots - one with what I've got now (Before.jpg) and what I'd like to have after the macro runs (After.jpg). The code below is what I used to get the first text to column breakout, which I can hopefully run on a loop to breakout anything in the DEPT column that contains a "/". It can ignore the rows that only have one department to begin with.

Selection.TextToColumns Destination:=Range("K2"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="/", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True

Eventually I'll want to create another macro that transposes the breakout text back into the DEPT column and populates it with the corresponding data, but I figure I'll take things one step at a time.

I think I've provided enough info for anyone who wants to assist, but if not let me know what else I can do. Thanks in advance for the help!

[TMS]

Why do you need to loop? Text to Columns works on a range, or column, you don't need to do it cell by cell.

Record a macro while you select the whole of column E and use Text to Columns to output to column K.


Regards, TMS

[JOHN H. DAVIS]

Does this help?

Please Login or Register  to view this content.

[ejb52]

TMS - Thanks for the reply. While I'm aware that I can select the range and do text to columns, I guess I was looking to add an additional element of automation to what is going to become a larger macro. The main piece of what I need to do involves the transposition step I mentioned above - basically cutting out the data in those breakout columns and pasting them back into that original DEPT column, inserting rows and populating the other fields accordingly. I wanted to have it so that I could just produce my data list (of variable length), run the macro, and have the text to columns/transposition happen all at once.

What you're saying makes sense though, and I guess there are other ways to go about the text to column piece. That being said, I'm going to need help with the transposition macro as well. Any ideas? I've attached another screenshot to show how I'd like the data to end up.

Thanks.

[TMS]

Any ideas ... post a sample workbook with data and code, and an explanation of what it is you need to achieve ... with pictures, if you must.

Have you tried John's code? Does that do what you want?

Regards, TMS

[ejb52]

John - This works perfect! Thanks for the code on that. If you have any input on the transposition piece I'm looking to do next, I'd love to hear from you.

[ejb52]

TMS - John's code works. Sample workbook coming soon. Sounds like pictures aren't the ideal? If not, just let me know. Please standby.

[JOHN H. DAVIS]

Try:

Please Login or Register  to view this content.

[ejb52]

John - That is exactly what I needed! I really appreciate the help! You saved me a lot of time and struggle. Cheers.

[TMS]

Sounds like pictures aren't the ideal?

No. If you provide a workbook, it is much easier to see what is required and also to test the possible solution(s).

Not everyone will create a test harness so they can develop the code, especially, if there are a lot of sheets and/or data.

But, seems John has provided you with what you need.

Regards, TMS

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback. Please comply with Forum Rule No. 9.

9. Acknowledge the responses you receive, good or bad. If your problem is solved, please say so clearly, and mark your thread as Solved: Click Thread Tools above your first post, select "Mark your thread as Solved". Or click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes. If more than two days have elapsed, the Dropdown option or Edit button will not appear -- ask a moderator to mark it.