splitting comma separated values

[aliceh]

I am trying to split comma seperated values using this code, which I have siccessfully used many times before;

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Sub SliceNDice() Dim objRegex As Object Dim X Dim Y Dim lngRow As Long Dim lngCnt As Long Dim tempArr() As String Dim strArr Set objRegex = CreateObject("vbscript.regexp") objRegex.Pattern = "^\s+(.+?)$" 'Define the range to be analysedX = Range([a1], Cells(Rows.Count, "b").End(xlUp)).Value2 Redim Y(1 To 2, 1 To 1000) For lngRow = 1 To UBound(X, 1) 'Split each string by "," tempArr = Split(X(lngRow, 2), ",") For Each strArr In tempArr lngCnt = lngCnt + 1 'Add another 1000 records to resorted array every 1000 records If lngCnt Mod 1000 = 0 Then Redim Preserve Y(1 To 2, 1 To lngCnt + 1000) Y(1, lngCnt) = X(lngRow, 1) Y(2, lngCnt) = objRegex.Replace(strArr, "$1") Next Next lngRow 'Dump the re-ordered range to columns C:D[c1].Resize(lngCnt, 2).Value2 = Application.Transpose(Y) End Sub

<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">I keep getting </code></pre>Runtim error '13':
Type mismatch
obviously there is something in my data that it does not like but I can't work out what.here is a sample of my data

Decorate 1- Not required| Front transfer-30X15cm:13| Front transfer-30x30 cm:15| Back transfer-30X15cm:13| Back transfer-30x30 cm:15, Decorate 2- Not required| Front transfer-30X15cm:13| Front transfer-30x30 cm:15| Back transfer-30X15cm:13| Back transfer-30x30 cm:15 any help will be most appreciated. Alice.

<tbody>
</tbody>

</code></pre>

 

[aliceh]

to clarify this is how my spreadsheet is set out.