Compile Error: Expected End Sub when using the following code to transpose and insert puntuation please help

[mukun]

I am using the code below which i got off this website in excel 2010.

When I try running the macros gives me the "compile error: expected end sub".

I am new to this.

Please help.

Sub Transfer()
Function konkat(ByVal r As Range, Optional s As String = ",")
For Each c In r
konkat = konkat & c.Value & s
Next
konkat = Left(konkat, Len(konkat) - Len(s))
End Function

 

[mukun]

I am trying to achieve this

A1:A8 = the digits 1 to 8

=konkat(A1:A8) returns[TABLE="width: 56"]
<colgroup><col width="56" style="width: 42pt;"></colgroup><tbody>[TR]
[TD="width: 56, bgcolor: transparent"]1,2,3,4,5,6,7,8[/TD]
[/TR]
</tbody>[/TABLE]


=konkat(A1:A8,"/") returns
[TABLE="width: 56"]
<colgroup><col width="56" style="width: 42pt;"></colgroup><tbody>[TR]
[TD="width: 56, bgcolor: transparent"]1/2/3/4/5/6/7/8

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Jonmo1]

Welcome to the board.

Your code as 2 declaration lines, one for a Sub, then one for a Function

Sub Transfer()
Function konkat(ByVal r As Range, Optional s As String = ",")
    For Each c In r
        konkat = konkat & c.Value & s
    Next
    konkat = Left(konkat, Len(konkat) - Len(s))
End Function

 

[MARK858]

Remove the Sub Transfer() line

Edit: toooooo slow

 

[mukun]

solved! appreciate the speed of response and the effectiveness!!!!

yes i realise my edit was slow lol. didnt know thats a thing.

thanks again