Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Hello,
For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Sub SplitData()
Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Hi there and thanks so much.
I tried this, but keep getting a syntax error. -- Thanks! Dee "Tom Ogilvy" wrote: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Probably because of wordwrap caused by the email:
Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub -- Regards, Tom Ogilvy "dee" wrote in message ... Hi there and thanks so much. I tried this, but keep getting a syntax error. -- Thanks! Dee "Tom Ogilvy" wrote: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Hi Tom,
I suspected it was the word wrap.. Thank you so much for your help. I ran the code and it worked for the first cell. I ran into trouble, though, when either the cell had 2 or 4 (something other than 3) entries in the cell and also when the same line in the cell next to it contained no data, but rather just the Alt+Enter as there was no quantity. Example: Dog Cat 100 Zebra Lion 45 Perhaps I'm asking for the impossible! Thanks again, Tom. I have to tell you that I have used some of your suggestions in other posts and they worked so well! Kind regards, Dee -- Thanks! Dee "Tom Ogilvy" wrote: Probably because of wordwrap caused by the email: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub -- Regards, Tom Ogilvy "dee" wrote in message ... Hi there and thanks so much. I tried this, but keep getting a syntax error. -- Thanks! Dee "Tom Ogilvy" wrote: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Maybe something like this:
Sub SplitData() Dim rng As Range, cell As Range Dim rng1 As Range Dim maxCell As Long Dim sz As Long, v As Variant maxCell = 1 For Each cell In Selection If InStr(1, cell, Chr(10), vbTextCompare) Then v = Split(cell, Chr(10)) sz = UBound(v) - LBound(v) + 1 If sz maxCell Then maxCell = sz cell.Resize(sz, 1).Value = Application.Transpose(v) Else If rng1 Is Nothing Then Set rng1 = cell Else Set rng1 = Union(rng1, cell) End If End If Next If Not rng1 Is Nothing Then For Each cell In rng1 cell.Resize(maxCell, 1).Value = cell Next End If End Sub Hard to tell without knowing all the possibilities. -- Regards, Tom Ogilvy "dee" wrote in message ... Hi Tom, I suspected it was the word wrap.. Thank you so much for your help. I ran the code and it worked for the first cell. I ran into trouble, though, when either the cell had 2 or 4 (something other than 3) entries in the cell and also when the same line in the cell next to it contained no data, but rather just the Alt+Enter as there was no quantity. Example: Dog Cat 100 Zebra Lion 45 Perhaps I'm asking for the impossible! Thanks again, Tom. I have to tell you that I have used some of your suggestions in other posts and they worked so well! Kind regards, Dee -- Thanks! Dee "Tom Ogilvy" wrote: Probably because of wordwrap caused by the email: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub -- Regards, Tom Ogilvy "dee" wrote in message ... Hi there and thanks so much. I tried this, but keep getting a syntax error. -- Thanks! Dee "Tom Ogilvy" wrote: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Divide Cell Across Rows
Hi Tom,
Thanks again. I tried this code and it worked to a degree, however, if I only have 1 or 3 entries in a cell, it copies that entry to the next row down and replaces what was there. If there are 2 entries, it separates the 2 entries properly, but then replaces the data in the 3rd row down with #N/A... Any ideas why that would happen? -- Thanks! Dee "Tom Ogilvy" wrote: Probably because of wordwrap caused by the email: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = _ Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub -- Regards, Tom Ogilvy "dee" wrote in message ... Hi there and thanks so much. I tried this, but keep getting a syntax error. -- Thanks! Dee "Tom Ogilvy" wrote: Sub SplitData() Dim rng As Range Set rng = Selection(1).Resize(1, 3) rng(1, 1).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 1), Chr(10))) rng(1, 2).Resize(3, 1).Value = Application.Transpose(Split(rng(1, 2), Chr(10))) rng(1, 3).Resize(3, 1).Value = rng(1, 3).Value End Sub worked on your sample data. -- Regards, Tom Ogilvy "dee" wrote: Hello, For example, I have 3 cells across by one row down and the first two contain data that is separated by Ctrl + Enter: Column: A B D Row: 1 Dog X 100 1 Cat Y 1 Mouse Z I am trying to fnd a way to split the data across 3 separate rows, in this example, as such: Column: A B C Row: 1 Dog X 100 2 Cat Y 100 3 Mouse Z 100 The data may sometimes be separated by another character, such as ; or , In an ideal world, I would love for the user to input which character or characters are the dividing character, but if this is too difficult, then I could simply provide different macros for different characters. Also, I don't want specific columns to be referred to, as the layout and column position of these items may change from file to file. Basically, for them to select the column(s) they wish to divide. Any help at all in this problem would be greatly appreciated. Thank you. -- Thanks! Dee |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how can i divide an xl sht into 2 sections with diff placed rows | Excel Discussion (Misc queries) | |||
To take the cells in the same row and divide them in rows | Excel Discussion (Misc queries) | |||
Looking for a formula that will divide a column & rows by 2 | Excel Worksheet Functions | |||
I Need to divide all cells in rows 2 and 3 by 100 | Excel Discussion (Misc queries) | |||
divide multiline text cell across several rows | Excel Programming |