Función Concatenar rango

[roberto]

Encontre esta función en la pagina:
http://www.danielklann.com/excel/user_defined_functions.htm
pero no hace lo que deberia de hacer, alguien podría ayudarme a corregirla?


Public Function ConcatenateRange(Separator As String, ParamArray ConcatRange() As Variant)
Dim vItem As Variant, rngCell As Range
Dim strSep As String
Dim vRetVal As Variant

For Each vItem In ConcatRange

For Each rngCell In vItem.Cells

vRetVal = vRetVal & rngCell.Value & strSep

Next rngCell

Next vItem

ConcatenateRange = Left$(vRetVal, Len(vRetVal))

End Function

 

[Greg Truby]

Hola Roberto,

En vez de corregir esa otra, le ofrezco la siguiente que agarré de aquí en Mr. Excel, ni me acuerdo en cual "hilo". Pero me parece más robusto porque uno puede usarlo como un UDF en una celula de Excel, o llamarla de VBA con un Array de valores.

<font face=Courier New>
<SPAN style="color:#007F00">'¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯¯</SPAN>
<SPAN style="color:#00007F">Function</SPAN> AConcat(varA <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, <SPAN style="color:#00007F">Optional</SPAN> strSep <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN> = "") <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#007F00">'______________________________________________________________________________</SPAN>

<SPAN style="color:#007F00">' by Harlan Grove, March 2002</SPAN>
    <SPAN style="color:#00007F">Dim</SPAN> varY <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
    <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> varA <SPAN style="color:#00007F">Is</SPAN> Range <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> varY <SPAN style="color:#00007F">In</SPAN> varA.Cells
            AConcat = AConcat & varY.Value & strSep
        <SPAN style="color:#00007F">Next</SPAN> varY
    <SPAN style="color:#00007F">ElseIf</SPAN> IsArray(varA) <SPAN style="color:#00007F">Then</SPAN>
        <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> varY <SPAN style="color:#00007F">In</SPAN> varA
            AConcat = AConcat & varY & strSep
        <SPAN style="color:#00007F">Next</SPAN> varY
    <SPAN style="color:#00007F">Else</SPAN>
        AConcat = AConcat & varA & strSep
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    AConcat = Left(AConcat, Len(AConcat) - Len(strSep))
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN>
</FONT>

 

[roberto]

Gracias de nuevo Greg.....