Split up delimited string & insert row below

[James]

Hi everyone. Im wondering how i could go about doing this. Ive setup a UDF
and one part looks like the following. A string is passed (rdText) and if it
contains a comma I need to break up the delimited text, insert however many
rows needed below where the formula is and paste the split up text there.
Thanks in advance

If InStr(1, rdText, ",") > 0 Then
For Each iChar In Split(rdText, ",")
If iChar <> "" Then
'insert row
'paste value to the new row
End If
Next iChar
End If

 

[Dave Peterson]

If you're calling the UDF from a cell on a worksheet, then you won't be able to
insert rows into your workbook.

Formulas (including your UDF) can bring back values to the cells that hold the
formulas (with minor exceptions).

But if you're using your UDF as a function that's called from a procedure,
then...

Dim mySplit As Variant
Dim HowMany As Long
Dim somecell As Range
Dim rdText As String 'my test data

rdText = "x,y,z,w"

Set somecell = ActiveSheet.Range("A1")

If InStr(1, rdText, ",", vbTextCompare) > 0 Then
mySplit = Split(rdText, ",")
HowMany = UBound(mySplit) - LBound(mySplit) + 1

somecell.Offset(1, 0).Resize(HowMany).EntireRow.Insert

somecell.Offset(1, 0).Resize(HowMany).Value = Application.Transpose(mySplit)
End If

(I had no idea where it was supposed to go, so I came down 1 row from a given
cell.)