Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split up delimited string & insert row below
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Split up delimited string & insert row below
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.) James wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create comma-delimited string from dynamic range? | Excel Discussion (Misc queries) | |||
Sum delimited values in text string if... | Excel Worksheet Functions | |||
Split non delimited data into multiple cells | Excel Worksheet Functions | |||
Copy tab delimited string to worksheet row - VB6 application | Excel Programming | |||
Parse a space delimited string into unique columns | Excel Programming |