More help on a UDF please. Urgent!

  • Thread starter Thread starter Excel Helps
  • Start date Start date
E

Excel Helps

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
 
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
 
Back
Top