Solved: Text to Rows

[Spratt_Cary]

I am attaching an example of a evaluation results spreadsheet. Yesterday I received help, but after testing, determined that the VB script, although great, will not work for this particular situation. And of course I need help, again.

Let me describe what is happening now. The VB script is separating multi-responses from one cell to individual responses in individual cells (responses separated with a ":") with a double click. Cells are added directly below the double clicked cell (multi-responses cell), Leaving the columns on the left and right unchanged.


The problem, the responses are not associated with a role type. (Question 1 in the example spreadsheet).

What is needed, once I double click on the multi-response cell, rows would be added, duplicating the information across the original row.

Don't know if this can be done? But I have seen amazing things come from this site.

This would allow us to see what users are actually doing and create more role specific training.

Any help will greatly appreciated.

Brenda

[Bob Phillips]

[vba]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ans
Dim Cels As Long, i As Long
Cancel = True
ans = Split(Target, ":")
Cels = UBound(ans)
Target.Offset(1).Resize(Cels).EntireRow.Insert shift:=xlDown
Rows(Target.Row).Copy Cells(Target.Row + 1, "A").Resize(Cels)
For i = 0 To Cels
Target.Offset(i) = ans(i)
Next
End Sub
[/vba]

[Spratt_Cary]

OK, I know that I ask for all the rows to be copied down. But that is not working for management. I have worked on this some today, but cannot figure it out. The attached spreadsheet is working as far as separating the responses into separate rows, but all I really need to copy along with the response rows is column "T", that shows the role of the user. Example in attached spreadsheet. Columns AD, AI and AN shows where I double clicked and separated the responses. Column "T" is the example of copying the role type. Hope you can help.
Thanks in advance
Brenda

[mdmackillop]

[VBA]Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Dim ans
Dim Cels As Long, i As Long
Cancel = True
ans = Split(Target, ":")
Cels = UBound(ans)
Target.Offset(1).Resize(Cels).EntireRow.Insert shift:=xlDown

'Just need column "T" to be copied down. Not entire row across.

Cells(Target.Row, "T").Resize(Cels + 1).FillDown

For i = 0 To Cels
Target.Offset(i) = ans(i)
Next
End Sub[/VBA]

[Spratt_Cary]

amazingly perfect, thanks

[mdmackillop]

We aim to please!