This is probably more thorough (complicated!) then need be. This
procedure will parse out the contents of the ActiveCell into cells based
on the value of MAX_ROWS_IN_OUTPUT :-
0 : values in the rows below the ActiveCell
1 : values in the columns below the ActiveCell
nn : values in a block of cells nn high
I hope it helps
Sean
"Just press the off switch, and go to sleep!"
'------------------------------------------------
'------------------------------------------------
Sub ParseDown()
Const MAX_ROWS_IN_OUTPUT = 0
Dim i As Long, k As String, r As Range, row_offset As Long,
column_offset As Long
k = ActiveCell.Value ' the thing to parse
i = InStr(k, ",") ' find the first comma
' if no commas, just return the original value
If (i = 0) Then
ActiveCell.Offset(1, 0).Value = k
GoTo Finished_ParseDown:
End If
row_offset = 0 ' put answer in row ?
column_offset = 0 ' put answer in column ?
Do Until (k = "")
' the resultant values are in a list underneath
' the source value in a list or a block
row_offset = row_offset + 1
Select Case MAX_ROWS_IN_OUTPUT
Case 0
' 0 means just a simple list
Case Else
If row_offset > MAX_ROWS_IN_OUTPUT Then
row_offset = 1
column_offset = column_offset + 1
End If
End Select
' check in case we run out of space
On Error GoTo Err_move_to_next_column
Set r = ActiveCell.Offset(row_offset, column_offset)
On Error GoTo 0
' check in case our source does not end with a comma
If (i = 0) And (k <> "") Then
r.Value = k
k = ""
Else
r.Value = Left(k, i - 1) ' get the latest value
k = Mid(k, i + 1) ' strip the value already gotten
End If
i = InStr(k, ",") ' find the next comma
Loop
Finished_ParseDown:
Exit Sub
Err_move_to_next_column:
row_offset = 1
column_offset = column_offset + 1
Resume
End Sub
'------------------------------------------------
'------------------------------------------------
