code to split cell data and enter into a new row

[Ronald Dodge]

Sounds like a job of the Split Function.

Dim myArr as Variant
Dim CS as String 'Hold the string value of the current cell being checked.
Dim CR as Long 'Current Row number to be checked for possible array.
Dim NR as Long 'Upper Bound element number of Array
'Lower Bound number in this instance will always be '0'
Dim FR as Long 'First Row number of range to be checked.
Dim LR as Long 'Last Row number of range to be checked.
Dim I as Long

FR = 2
LR = 1194

For CR = FR to LR
CS = Range("A" & CR).Value
If InStr(1,CS,",",0)>0 Then
myArr = Split(CS,",")
NR = UBound(myArr)
Range("A" & CR).Offset(1,0).Resize(NR,11).Insert(xlShiftDown)
For I = 0 to NR
Range("A" & CR + I).Value = myArr(I)
Next I
CR = CR + NR
LR = LR + NR
End If
Next CR

The above code assumes it's going to be done on the Active Worksheet given
there is no qualified statement before the Range Object.

--
Ronald R Dodge, Jr
Production Statistician
Master MOUS 2000

"Trevor" <trevor.r...@cibc.ca> wrote in message
news:21c701c28cc9$a7ffceb0$8af82ecf@TK2MSFTNGXA03...
> Hi,
>
> I'm using Excel2000
>
> I have an excel sheet with columns A - K, and rows 1 -
> 1194. The first row is a header row. Some of rows
> contain data separated by commas. I would like the code
> to check each cells A2:A1194, if the value contains a
> comma I then want the code to split the contents of cells A
> (row with comma):K(row with comma), for each split value
> (it will be 2, 3, or 4) I want a new row inserted after
> the current row and the corresponding values put into the
> new row.
>
> I haven't done any coding in Excel's VBA prior to this,
> only very limited coding in Outlook's VBA. Any ideas to
> help me get going with the code will be greatly
> appreciated.
>
> Thanks,
>
> Trevor
>

[Trevor]

Thanks for the great help, I think it needs one more thing:

When the 'If' procedure finds a comma in a cell in the
first column, it needs to do the work noted across the
entire row from A1:K1 - and as a tricky part, some of the
other cells in the row that has been found will not
contain a comma.

I'm going to play a bit with what you've given me though -
it's a great start to what I need done.

Thanks,

Trevor

>.
>

[Trevor]

I'm thinking I could place something like 'for each cell
in range crA:crK' after the 'if' and before the 'myArr' -

would this work?

Thanks again,

>.
>