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 |
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 |
All times are GMT +1. The time now is 08:48 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com