Complicated splitting cells into multiple cells for multiple cases
hello
I would split cells for multiple cells . so split the items into cell are different counts . they contain 3,4,5,6 items and it's one space among the items . case1
if the cells contain three items , then should split for each item into each cell separately as in row2,3 in sheet split . case2
if the cells contain four items , then should be the first ,second items in column B and the third item should be in column C and the fourth item should be in column D see the row 4 in sheet split case3
if the cells contain five items ,then should be the first ,second items in column B and third,fourth items should be in column C and the fifth item should be in column D see the row 4 in sheet split case4
if the cells contain six items , then should be the first ,second items in column B and the third,fourth items should be in column C and the fifth,sixth items should be in column D see the row 5,6 in sheet split
the result should be in the first sheet in the same range based on what I put in the second sheet and also I would add others sheets with the same structure . so the macro should be flexible .
Re: Complicated splitting cells into multiple cells for multiple cases
awesome ! this is exactly what I want and sorry about mistake the second item in row 3 for sheet SPLIT .I corrected and edited file . based on my explanation seem to you understood me what I want , but as I said
would add others sheets with the same structure . so the macro should be flexible .
so I would implement for specific sheets like (ITE,SH1,SH2,MN)
last thing I would split the data just form the first time run the macro without show any error. your code split data repeatedly when run the macro for more than one time .
According to your attachment a VBA demonstration for starters (v2) :
PHP Code:
Sub Demo1() Dim Ws As Worksheet, V, R&, S, P&, C% For Each Ws In Worksheets If Ws.[A1&"¤"&B1&"¤"&C1&"¤"&D1="S.N¤ITEM¤QTY¤"] Then With Ws.[A1].CurrentRegion.Columns .Item("C:D").Insert With .Item("B:D") V = Application.Trim(.Value2): V(1, 2) = "TYPE": V(1, 3) = "MODEL" For R = 2 To .Rows.Count S = Split(V(R, 1)) If UBound(S) > 1 And UBound(S) < 6 Then P = 0 For C = 1 To 3 V(R, C) = S(P) If UBound(S) - P > 3 - C Then P = P + 1: V(R, C) = V(R, C) & " " & S(P) P = P + 1 Next End If Next .Value2 = V .AutoFit End With End With End If Next End Sub
► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄
Last edited by Marc L; 03-19-2022 at 09:01 PM.
Reason: v2
Re: Complicated splitting cells into multiple cells for multiple cases
@Marc L excellent ! just I would implement your code for specific sheets(ITE,SH1,SH2,MN), not all of the sheets in workbook .
how can achieve it,please?
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!!!
Re: Complicated splitting cells into multiple cells for multiple cases
I didn't misread . my knowladge in vba is too poor.
this line
HTML Code:
apply to every sheet in the workbook, as far as I know .
didn't you see the If Join codeline ? The reason why a specific sheets array seems useless …
if you see this seems useless . your code should work for the sheets contain the same structure as the first sheet .
otherwise shouldn't show error mismatch when there is sheet not relating of data as the first sheet .
and if you mean I have to change this line
LinkBack URL
About LinkBacks
Register To Reply
and sorry about mistake the second item in row 3 for sheet SPLIT .I corrected and edited file . based on my explanation seem to you understood me what I want , but as I said 
Hello, try this !
Re: Complicated splitting cells into multiple cells for multiple cases
Originally Posted by Ali-M
Bookmarks