how to split copy the cells/entire row to another sheet?

[Sarathy]

Hi,
I am very new to excel.
I am trying to split the string between the delimiters
And I want the splitted output to be printed in the another sheet.
I am giving input in sheet named "Split", as

A1 A2 A3 A4
a/b/c 10 10 10
d/e/f 20 20 20
g/h 30 30 30


And I want the output in another sheet named "Result".. The expected output is follows.

A1 A2 A3 A4

a 10 10 10
b 10 10 10
c 10 10 10
d 20 20 20
e 20 20 20
f 20 20 20
g 30 30 30
h 30 30 30

My macro is
Sub SplitCells()
Dim fromCol As String
Dim toCol As String
Dim fromRow As String
Dim toRow As String
Dim inVal As String
Dim outVal As String
Dim slashPos As Integer


' Copy from range A1 to range A5.'
fromCol = "A"
fromRow = "1"
toCol = "A"
toRow = "5"



' Go until no more entries in column A.'
inVal = Range(fromCol + fromRow).Value
While inVal <> ""

' Go until all sub-entries used up.'
While inVal <> ""
Range(fromCol + fromRow).Select

' Extract each subentry.'
slashPos = InStr(1, inVal, "/")
While slashPos <> 0

' and write to output column.'
outVal = Left(inVal, slashPos - 1)
Range(toCol + toRow).Select
Rows(1).EntireRow.Copy
Selection.PasteSpecial
Range(toCol + toRow).Value = outVal
toRow = Mid(Str(Val(toRow) + 1), 2)



' Remove that sub-entry.'
inVal = Mid(inVal, slashPos + 1)
While Left(inVal, 1) = " "
inVal = Mid(inVal, 2)
Wend
slashPos = InStr(1, inVal, "/")
Wend

' Get last sub-entry (or full entry if no slash).'
Range(toCol + toRow).Select
Rows(1).EntireRow.Copy
Selection.PasteSpecial
Range(toCol + toRow).Select
Range(toCol + toRow).Value = inVal
toRow = Mid(Str(Val(toRow) + 1), 2)
inVal = ""
Wend

' Advance to next source row.'
fromRow = Mid(Str(Val(fromRow) + 1), 2)
Range(fromCol + fromRow).Select
inVal = Range(fromCol + fromRow).Value
Wend


End Sub
Please do help me with right modification in the above code to achieve my output....

Thank s lot in advance

 

[MickG]

I'm assuming where you have "A1 ,A2,A3" etc you mean "A1,B1, C1" etc ???
If so try this :-

[COLOR="Navy"]Sub[/COLOR] MG11Nov39
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Dim[/COLOR] Del  [COLOR="Navy"]As[/COLOR] Variant
[COLOR="Navy"]Dim[/COLOR] n [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] c [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Ac [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("A1"), Range("A" & Rows.Count).End(xlUp))
    [COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
        Del = Split(Dn, "/")
        [COLOR="Navy"]For[/COLOR] n = 0 To UBound(Del)
            c = c + 1
            [COLOR="Navy"]For[/COLOR] Ac = 1 To 4
                Sheets("Result").Cells(c, Ac) = IIf(Ac = 1, Del(n), Dn(, Ac))
            [COLOR="Navy"]Next[/COLOR] Ac
        [COLOR="Navy"]Next[/COLOR] n
    [COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[Sarathy]

@Mick: Thanks a lot Mick, this is what I expected.. Thank you very much..

 

[Sarathy]

The above code is for, splitting the data in A1 cell and to copy the entire row to another sheet right?
Then, how can I split C1 cell and copy the entire row (say A1,B1,C1,D1) to another sheet?

 

[MickG]

The code actually splits all the data from column "A" to Sheet "Result" , in the format desired.
I you want Cell "C1" split to another sheet, can you show me a before and after example and the relevant cell addresses.
Mick

 

[Sarathy]

Mick, the scenerio is given below:

In "Sheet_1", I am giving input as follows:

A B C D E
1 Ram Chennai 10/03/1989 a/b/c First
2 Sam Mumbai 12/5/1980 d/e/f Second
3 Kumar Kolkatta 14/8/1999 g/h Third



In "Sheet_2", I need the output as follows:


A B C D E
1 Ram Chennai 10/03/1989 a First
2 Ram Chennai 10/03/1989 b First
3 Ram Chennai 10/03/1989 c First
4 Sam Mumbai 12/5/1980 d Second
5 Sam Mumbai 12/5/1980 e Second
6 Sam Mumbai 12/5/1980 f Second
7 Kumar Kolkatta 14/8/1999 g Third
8 Kumar Kolkatta 14/8/1999 h Third

Note: Only the D column should get splitted, not the C column(Date)

Sarathy

 

[MickG]

This appears to be a duplicate post that has been answered.?????