PDA

View Full Version : Solved: Excel - Text to rows



Spratt_Cary
07-07-2009, 02:01 PM
I have a questionnaire that end users are completing. A few of the questions are multiple choice and they can choose as many of the responses that applies. The problem is, the application that pulls the data, puts all the responses in one cell divided with a colon. This makes it very difficult to calculate responses. Is there a way to extract these responses individually and insert a new cell(s) below the original in the same column without overwriting the data. There can be one response or 8 responses. See Attached example. column AO row 7, column AU, row 6. Thanks in advance for any help. (I have not been on VBA for awhile, feels good to be back)
Brenda

mdmackillop
07-07-2009, 02:28 PM
Can you post a "desired result" sample?

mdmackillop
07-07-2009, 02:40 PM
Try this.
Paste the code in the worksheet module. Doubleclick the multi-answer cell.
EDIT: You'll need to turn off the Autofilter to allow insertion of cells.

Option Explicit
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).Insert shift:=xlDown
For i = 0 To Cels
Target.Offset(i + 1) = ans(i)
Next
End Sub

Spratt_Cary
07-07-2009, 04:30 PM
Not sure what I am doing wrong, but it does not seem to work for me. URGH!!!!!!!!!!

Spratt_Cary
07-07-2009, 04:31 PM
Thanks mdmackillop, not sure what I am doing wrong. see attached
brenda

mdmackillop
07-07-2009, 04:37 PM
Try this

Spratt_Cary
07-07-2009, 04:52 PM
Man, you are very very good.

cdc555
02-13-2013, 08:47 AM
hello

I need the exact same thing I think - I am very new to VBA - would it be possible to have a copy of your working example?
thank you, thank you in advance