Split cell with Carriage returns, then loop code for only specific sheets

[DK643]

Hi,
I need some help adding a loop to an existing code and can't figure it out. Newbie here.

Current: I have a single cell (a9), that has hundreds of results. Each result has a carriage return after it. I need each result to be split out having their own row.
The current code basically does a text to columns and then a transposes the data in column A starting with cell A9.

Requested help:
1. I would like to have this code to loop and execute on specific sheets only. (in particular sheets 11-18)
2. Instead of the resulting data starting in A9, I would like the resulting data to start in G1, or a cell on a summary sheet.
3. Finally, it would be nice if the result removed the blank rows between the earnings.

Cell	Data
A9	Earning 1 Earning 2 Earning 3

Any help you could provide would be wonderful. Thank you!!

D

Code that doesn't work today: It breaks at sh.[a9] because I don't know what to do at that point.

Sub Loopforselectsheetsonly()
Dim sh As Worksheet
For Each sh In Sheets
' This starts the VBA loop to execute the VBA split only on assigned Sheets
Select Case sh.Name
Case Is = "Sheet11", "Sheet12", "Sheet13"
sh.[a9].
'Not sure what this does. Need this to execute on the below code, then loop back and do it on all assigned sheets.
End Select
Next sh
End Sub

Sub Splitcelldatawithcarriagereturn()
'VBA code to split out cell that has countless data with carriage returns
'Separates on carriage return, then transposes data. Result = vertical list
Dim Rng As Range
Dim WorkRng As Range
On Error Resume Next
Set WorkRng = Range("a9")
For Each Rng In WorkRng
lLFs = VBA.Len(Rng) - VBA.Len(VBA.Replace(Rng, vbLf, ""))
If lLFs > 0 Then
Rng.Offset(1, 0).Resize(lLFs).Insert shift:=xlShiftDown
Rng.Resize(lLFs + 1).Value = Application.WorksheetFunction.Transpose(VBA.Split(Rng, vbLf))
End If
Next
End Sub

 

[jdellasala]

Like this?

Book1
	A	B
1	Cell	Data
2	A9	Earnings 1 Earnings 2 Earnings 3 Earnings 4 Earnings 5 Earnings 6 Earnings 7 Earnings 8 Earnings 9 Earnings 10
3
4
5
6	Cell	Data
7	A9	Earnings 1
8	A9	Earnings 2
9	A9	Earnings 3
10	A9	Earnings 4
11	A9	Earnings 5
12	A9	Earnings 6
13	A9	Earnings 7
14	A9	Earnings 8
15	A9	Earnings 9
16	A9	Earnings 10
Sheet1

That took less than 2 minutes (which never has to be done again) with Power Query and only 2 lines of M Code using the UI:

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Split Column by Delimiter" = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Data", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Data")
in
    #"Split Column by Delimiter"

Best part is as the original table grows, the resulting table can just be Refreshed. Just don't put anything under the original table!

 

[DK643]

Thank you for your response!! Your last comment made me realize that I need to follow a more strategic approach to setup the data for efficient analysis and scalability. This makes it much more complex but it may really help other folks if they have a similar data extract to work from. Hopefully you can help.

See below: The data is in three columns. All three columns have the potential to have data with carriage returns between the data. The rows will be different over time, so placing the results below won't work. We will need to put them out to the side. My original plan is not going to work.

I seek to flatten it so there is one line for each Jurisdiction and all the attributes are on the same line. Then repeat for the next row, and place the results below it so it is all in one simple list.
I am unable to use the L2BB due to permissions, so sending a picture.
Hope you can help and thank you !

 

[jdellasala]

Thank you for your response!! Your last comment made me realize that I need to follow a more strategic approach to setup the data for efficient analysis and scalability. This makes it much more complex but it may really help other folks if they have a similar data extract to work from. Hopefully you can help.

See below: The data is in three columns. All three columns have the potential to have data with carriage returns between the data. The rows will be different over time, so placing the results below won't work. We will need to put them out to the side. My original plan is not going to work.

I seek to flatten it so there is one line for each Jurisdiction and all the attributes are on the same line. Then repeat for the next row, and place the results below it so it is all in one simple list.
I am unable to use the L2BB due to permissions, so sending a picture.
Hope you can help and thank you !

Guess you didn't read my Signature. Post the two tables using XL2BB.

 

[Fluff]

Guess you didn't read what the Op said

I am unable to use the L2BB due to permissions, so sending a picture.

 

[jdellasala]

Guess you didn't read what the Op said

I'll put together a very small or easy table myself sometimes, and MAYBE the Op doesn't have sufficient "permission" to add an Add-in (that would require a very well managed, fairly large infrastructure), but can't do that but CAN post a screenshot? Sloppy security if so. It doesn't make sense.

 

[Fluff]

that would require a very well managed, fairly large infrastructure

It's pretty simple & lots of companies do it.

CAN post a screenshot? Sloppy security if so. It doesn't make sense.

Totally different things. One is allowing macro enabled files onto the system, the other is not.

 

[jdellasala]

It's pretty simple & lots of companies do it.


Totally different things. One is allowing macro enabled files onto the system, the other is not.

I worked in IT Support for a major Accounting Firm for 27 years - last 5 doing Inventory Management and control for over 350K mobile devices, and over 100K Carrier accounts across 4 carriers. If a company is worried enough about Add-Ins, they'd block being able to make a screenshot if they really wanted to be THAT secure. It's not unlike DUMB banking sights that put "protections" on PDF statements - like they can't be "printed" to another format, and then that can be "printed" again to an open PDF.

 

[jdellasala]

From this Table:

Book1
	A	B	C
1	Jurisdiction	Text1	Text2
2	State 1 State 2	1 2 3	a b
3	State 7 State 8	xxx YYY ZZZ
4	State A State B State C State D	555 666 777 888	Test 1 Test 2
Sheet1

Using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="tblMessy"]}[Content],
    SplitJurisdictionColumn = Table.ExpandListColumn(Table.TransformColumns(Source, {{"Jurisdiction", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), let itemType = (type nullable text) meta [Serialized.Text = true] in type {itemType}}}), "Jurisdiction"),
    SplitText1Column = Table.SplitColumn(SplitJurisdictionColumn, "Text1", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Text1.1", "Text1.2", "Text1.3", "Text1.4"}),
    SplitText2Column = Table.SplitColumn(SplitText1Column, "Text2", Splitter.SplitTextByDelimiter("#(lf)", QuoteStyle.Csv), {"Text2.1", "Text2.2"})
in
    SplitText2Column

to get this

Book1
	A	B	C	D	E	F	G
6	Jurisdiction	Text1.1	Text1.2	Text1.3	Text1.4	Text2.1	Text2.2
7	State 1	1	2	3		a	b
8	State 2	1	2	3		a	b
9	State 7	xxx	YYY	ZZZ
10	State 8	xxx	YYY	ZZZ
11	State A	555	666	777	888	Test 1	Test 2
12	State B	555	666	777	888	Test 1	Test 2
13	State C	555	666	777	888	Test 1	Test 2
14	State D	555	666	777	888	Test 1	Test 2
Sheet1

You might want to check out my post here on Macro security. That may resolve the problem you're having with XL2BB.