I've been trying to modify a UDF that I built a while back with the help of this board. The function concatenates cells within a range and places a separator between each of the values. The modification prevents the separator from being inserted between cells without a value. The function is almost perfect with this code:
Code
Function ConcatenateRange(Parts As Range, Separator As String)
' Build a single string from a passed range with a
' passed separator between each value
Dim strTemp, sepTemp As String
Dim cel As Range
Dim cnt As Integer
strTemp = ""
For Each cel In Parts.Cells
If cel.Value = "" Or cel.Value = 0 Then
sepTemp = ""
Else
sepTemp = Separator
End If
strTemp = strTemp & sepTemp & cel.Value
Next cel
ConcatenateRange = strTemp
End Function
The only issue is that it usually inserts a separator at the front of the string. I added a test for using the separator the first time (additional code is bold). The code doesn't work. Instead of removing the first occurence of the separator, it removes all occurences.
Code
Function ConcatenateRange(Parts As Range, Separator As String)
' Build a single string from a passed range with a
' passed separator between each value
Dim strTemp, sepTemp As String
Dim cel As Range
Dim cnt, [B]firstSep[/B] As Integer
[B]firstSep = 0[/B]
strTemp = ""
For Each cel In Parts.Cells
If cel.Value = "" Or cel.Value = 0 [B]Or firstSep = 0 [/B]Then
sepTemp = ""
Else
sepTemp = Separator
[B]firstSep = 1[/B]
End If
strTemp = strTemp & sepTemp & cel.Value
Next cel
ConcatenateRange = strTemp
End FunctionCould someone point out the error of my ways? Thanks.
Steve