help with the VB script
 

I need to run the script for concantenating the cells.

For eg: If I have 10 serial numbers in A1:A10, these numbers should
accompany with the ",". the result should come as 1,2,3,4,5,6,7,8,9,10

Often working with the formula is difficult, so I have planned to run macros.

Could any one pls guide me with the script.

help with the VB script
 

Try this :

in cell: =MyConcatenate(A1:A10) or any range

Function MyConcatenate(ByRef myrng As Range)
mystr = ""
For Each Cell In myrng
mystr = mystr + Str(Cell.Value) & ","
Next
MyConcatenate = Left(mystr, Len(mystr) - 1)
End Function

HTH

"Igneshwara reddy" wrote:

I need to run the script for concantenating the cells.

For eg: If I have 10 serial numbers in A1:A10, these numbers should
accompany with the ",". the result should come as 1,2,3,4,5,6,7,8,9,10

Often working with the formula is difficult, so I have planned to run macros.

Could any one pls guide me with the script.

help with the VB script
 

Have a look at the =CONCATENATE function
or =A1&","&A2&","&... you get the idea

cheers
Simon

Blog: www.smurfonspreadsheets.net
Website: www.codematic.net Excel development, support and training


"Igneshwara reddy" wrote in
message ...
I need to run the script for concantenating the cells.

For eg: If I have 10 serial numbers in A1:A10, these numbers should
accompany with the ",". the result should come as 1,2,3,4,5,6,7,8,9,10

Often working with the formula is difficult, so I have planned to run
macros.

Could any one pls guide me with the script.

help with the VB script
 

Hi, thanks for the advise.

But I am unable to run that script, see below which I have show you how my
script is showing.

Regards,

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/7/2007 by ir230005
'
' =MyConcatenate(A1:A10)
' Function MyConcatenate(ByRef myrng As Range)
' mystr = ""
' For Each Cell In myrng
' mystr = mystr + Str(Cell.Value) & ","
' Next
' MyConcatenate = Left(mystr, Len(mystr) - 1)


End Sub

"Toppers" wrote:

Try this :

in cell: =MyConcatenate(A1:A10) or any range

Function MyConcatenate(ByRef myrng As Range)
mystr = ""
For Each Cell In myrng
mystr = mystr + Str(Cell.Value) & ","
Next
MyConcatenate = Left(mystr, Len(mystr) - 1)
End Function

HTH

"Igneshwara reddy" wrote:

I need to run the script for concantenating the cells.

For eg: If I have 10 serial numbers in A1:A10, these numbers should
accompany with the ",". the result should come as 1,2,3,4,5,6,7,8,9,10

Often working with the formula is difficult, so I have planned to run macros.

Could any one pls guide me with the script.

help with the VB script
 

You put =Myconcatenate(A1;A10) in the cell e.g B1 ,where you want the result
to appear (just like any Excel function).

Simply copy/paste the code I posted into a VBE module .. it's a function not
a subroutine.

HTH

"Igneshwara reddy" wrote:

Hi, thanks for the advise.

But I am unable to run that script, see below which I have show you how my
script is showing.

Regards,

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 3/7/2007 by ir230005
'
' =MyConcatenate(A1:A10)
' Function MyConcatenate(ByRef myrng As Range)
' mystr = ""
' For Each Cell In myrng
' mystr = mystr + Str(Cell.Value) & ","
' Next
' MyConcatenate = Left(mystr, Len(mystr) - 1)


End Sub

"Toppers" wrote:

Try this :

in cell: =MyConcatenate(A1:A10) or any range

Function MyConcatenate(ByRef myrng As Range)
mystr = ""
For Each Cell In myrng
mystr = mystr + Str(Cell.Value) & ","
Next
MyConcatenate = Left(mystr, Len(mystr) - 1)
End Function

HTH

"Igneshwara reddy" wrote:

I need to run the script for concantenating the cells.

For eg: If I have 10 serial numbers in A1:A10, these numbers should
accompany with the ",". the result should come as 1,2,3,4,5,6,7,8,9,10

Often working with the formula is difficult, so I have planned to run macros.

Could any one pls guide me with the script.