VBA Macro to split data

[Shwapx]

Hello All,

I'm trying to create a macro which splits data out of textjoin formula and output it as list.

I have created a macro which do that, but it's not moving the cells and the data is not correct. So I need to adjust the macro to add empty cell in column G to keep up with the data.

Here is my macro at the moment:

Sub SeparateDataForEachCell()
Dim lastRow As Long
Dim dataRange As Range
Dim cell As Range
Dim dataString As String
Dim dataArray() As String
Dim outputRange As Range
Dim outputCell As Range
Dim i As Integer

lastRow = Cells(Rows.Count, "I").End(xlUp).Row
Set dataRange = Range("I1:I" & lastRow)

For Each cell In dataRange
dataString = cell.Value

dataArray = Split(dataString, " / ")

Set outputCell = cell.Offset(0, -1)

For i = LBound(dataArray) To UBound(dataArray)
outputCell.Value = dataArray(i)
Set outputCell = outputCell.Offset(1)
Next i
Next cell
End Sub

Here is an example table:

Book1
	G	H	I
1	Test	ABC	ABC
2	Test23	ABC	ABC / ABV / AB23
3	Test24	ABB	ABB / AVV / AWW
4	Test25	ACC	ACC / ATT / AWQ / AUU
5		ATT
6		AWQ
7		AUU
8	Up you can see the output which I'm getting now
9	Below is the ouput which I'm looking for
10	Test	ABC
11	Test23	ABC
12		ABV
13		AB23
14	Test24	ABB
15		AVV
16		AWW
17	Test25	ACC
18		ATT
19		AWQ
20		AUU
Sheet1

 

[Rick Rothstein]

Assuming the "Test" text is in cells G1:G4 and the slash delimited text is in cells I1:I4, then this formula will generate the desired output (no VBA required)...

=TEXTSPLIT(TEXTJOIN("/",,SUBSTITUTE(G1:G4&"-"&I1:I4," / ","/-")),"-","/")

 

[Shwapx]

Assuming the "Test" text is in cells G1:G4 and the slash delimited text is in cells I1:I4, then this formula will generate the desired output (no VBA required)...

=TEXTSPLIT(TEXTJOIN("/",,SUBSTITUTE(G1:G4&"-"&I1:I4," / ","/-")),"-","/")

So I can use that formula and use my results in G and I columns as help to split it as I want. Thanks I will try and report.

 

[Shwapx]

I just tried, but it's giving me #Spill error, is it because my results are coming from formula?

 

[Rick Rothstein]

Where are you putting this formula at? A #Spill error means you have data or formulas in the output range. The output from my formula is two columns... the first with the properly spaced "Test.." text and the second with the split apart text.

 

[Shwapx]

I think a macro would be better approach If I able to include in the macro to add cell for each item splitted in

Where are you putting this formula at? A #Spill error means you have data or formulas in the output range. The output from my formula is two columns... the first with the properly spaced "Test.." text and the second with the split apart text.

I've made it to work, but still it's not working in my real case. Since I have another column in the middle and I actually have to move 2 columns when we do the split otherwise one of the row is staying high and one is going down so data is not correct. That's why I was thinking if I can add cells only in the columns.

 

[Rick Rothstein]

I was a little confused about your actual existing data originally... and I still am. What does your EXISTING data actually look like?

 

[Shwapx]

I was a little confused about your actual existing data originally... and I still am. What does your EXISTING data actually look like?

I hope this helps a bit.

Book1
	G	H	I	J
10	Header1	Header2	Header3
11	Test	Test1	ABC
12	Test23	Test2	ABC / ABV / AB23
13	Test24	Test3	ABB / AVV / AWW
14	Test25	Test4	ACC / ATT / AWQ / AUU
15
16	If I don't have data in header 1 I won't have data inside other headers as well so.
17
18	All of the data under these headers is coming from formulas I can move them outisde my result and work with your formula on their place, but I'm getting this result since it's moving only one row and not the two (I have adjusted it to look into column H and I) so column G is falling behind.
19	Test	Test1	ABC
20	Test23	Test2	ABC
21	Test24		ABV
22	Test25		AB23
23		Test3	ABB
24			AVV
25			AWW
26		Test4	ACC
27			ATT
28			AWQ
29			AUU
Sheet1

 

[Rick Rothstein]

I'm still not clear on the output you need. Columns H and I look like what you asked for... why is Column G there at all? If it is from formulas, can't you just put the formulas somewhere else? I think the problem I am having is in understanding fully what you have and exactly what you want from it.

 

[Shwapx]

I'm still not clear on the output you need. Columns H and I look like what you asked for... why is Column G there at all? If it is from formulas, can't you just put the formulas somewhere else? I think the problem I am having is in understanding fully what you have and exactly what you want from it.

Column G should move with Column H since they are part of the result and they are linked. Since we move with the formula down column H G is falling behind.