how to count within a cell

[ibcarolek]

I need to count the number of commas within a cell so I can insert the proper number of rows below before I parse the value into separate rows. For instance,

box, animal, elephant, wood, storage

I need to know that there are 4 commas.
So I can then insert 4 rows below the line and run the following macro:

For i = 1 To Selection.Rows.Count
Dim splitValues As Variant
splitValues = Split(Selection.Rows(i).Value, ",")
Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)
Next i

to end up with

box
animal
elephant
wood
storage

thanks for any/all help!

 

[Weaver]

you could just count the number of elements in your 'slpitValues' array.

newRowCount = ubound(splitValues)+1, if your arrays are set to begin at element 0.

 

[VoG]

You shouldn't need to select anything. Can you post the complete code.

 

[VoG]

If you are looking at a particular cell then perhaps

NCommas = Len(Selection.Value) - Len(Replace(Selection.Value, ",", ""))

 

[ibcarolek]

VoG, you rock!
Your formula inserts the right number of rows.
So what I got is:

Dim i As Long
Dim Ncommas As Long
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False

Ncommas = Len(Selection.Value) - Len(Replace(Selection.Value, ",", ""))
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(Ncommas, 0)).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
For i = 1 To Selection.Rows.Count
Dim splitValues As Variant
splitValues = Split(Selection.Rows(i).Value, ",")
Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
ActiveCell.Offset(0, -1).Select
End With

Thanks!

 

[ibcarolek]

sorry...that's

Dim i As Long
Dim Ncommas As Long 
With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
 
Ncommas = Len(Selection.Value) - Len(Replace(Selection.Value, ",", ""))
Range(ActiveCell.Offset(1, 0), ActiveCell.Offset(Ncommas, 0)).Select
Selection.EntireRow.Insert
ActiveCell.Offset(-1, 0).Select
For i = 1 To Selection.Rows.Count
Dim splitValues As Variant
splitValues = Split(Selection.Rows(i).Value, ",")
Selection.Rows(i).Resize(UBound(splitValues) - LBound(splitValues) + 1).Value = Application.Transpose(splitValues)
Next i
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
ActiveCell.Offset(0, -1).Select
End With