Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More help on a UDF please. Urgent!
Could one of the kind people that helped me over the weekend advise me
whether the code below can be altered to give this result please? data1¦data1|data2¦data2|data3¦data3 where data1 has been entered in one column,data2 in one column, data3 in one column but the output from the code duplicates data1,2,and 3 and also puts alternating broken and solid pipes inbetween. Thank you Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String Dim pipe As Boolean pipe = True For Each Cell In CellBlock pipe = Not pipe If Len(Cell.Text) 0 Then If pipe = False Then sbuf = sbuf & Cell.Text & Chr(166) Else sbuf = sbuf & Cell.Text & "|" End If End If Next ConCatRange = sbuf End Function |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
More help on a UDF please. Urgent!
Does this work?
Function ConCatRange(CellBlock As Excel.Range) As String Dim Cell As Excel.Range Dim sbuf As String For Each Cell In CellBlock If Len(Cell.Text) Then sbuf = sbuf & Cell.Text & Chr(166) & Cell.Text & "|" End If Next Cell ConCatRange = Left$(sbuf, Len(sbuf) - 1) End Function On Jan 22, 7:09*pm, Excel Helps wrote: Could one of the kind people that helped me over the weekend advise me whether the code below can be altered to give this result please? data1¦data1|data2¦data2|data3¦data3 where data1 has been entered in one column,data2 in one column, data3 in one column but the output from the code duplicates data1,2,and 3 and also puts alternating broken and solid pipes inbetween. Thank you Function ConCatRange(CellBlock As Range) As String Dim Cell As Range Dim sbuf As String Dim pipe As Boolean pipe = True * * For Each Cell In CellBlock * * pipe = Not pipe * * * * If Len(Cell.Text) 0 Then * * * * * * If pipe = False Then * * * * * * sbuf = sbuf & Cell.Text & Chr(166) * * * * * * Else * * * * * * sbuf = sbuf & Cell.Text & "|" * * * * * * End If * * * * End If * * Next * * ConCatRange = sbuf End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
URGENT HELP PLEASE | Excel Worksheet Functions | |||
help - urgent | Excel Discussion (Misc queries) | |||
Urgent!!! | Excel Discussion (Misc queries) | |||
Urgent-Urgent VBA LOOP | Excel Discussion (Misc queries) | |||
not urgent | Excel Discussion (Misc queries) |