Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
subroutine to concatenate strings
I want to write a subroutine/macro that will execute on a selected range of
cells ( of a single column ). That range will be passed to the subroutine and the sub will concatenate all the values of the cells within the range with commas "," seperating each value. And finally the sub will place the resulting string say in cell A1. for example If I select range A4:A6 which contain values 4,5,6 respectively then when I run the macro/sub the resulting string "4,5,6" should appear in A1. how can that be done. I can do the concatenation part but dont know about working on selected ranges. Note: The range sould be passed as argument to the sub and the range does not span multiple columns. thx |
#2
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
subroutine to concatenate strings
Hi MAB,
Try: Sub Concat(RngConcat As Range, CellDest As Range) Dim cel As Range Dim sStr As String For Each cel In RngConcat If Len(sStr) 1 Then sStr = sStr & "," & cel.Value Else sStr = cel.Value End If Next cel CellDest = sStr End Sub As an example of usage: Sub Demo() 'Add new sheet to avoid problems with existing data! Sheets.Add 'To avoid problems with existing data! 'Write demo data Range("A4") = "Rum" Range("A5") = "Brandy" Range("A6") = "Vodka" 'And here we concatenate data to A1 Concat Range("A4:A6"), Range("A1") End Sub --- Regards, Norman "MAB" wrote in message ... I want to write a subroutine/macro that will execute on a selected range of cells ( of a single column ). That range will be passed to the subroutine and the sub will concatenate all the values of the cells within the range with commas "," seperating each value. And finally the sub will place the resulting string say in cell A1. for example If I select range A4:A6 which contain values 4,5,6 respectively then when I run the macro/sub the resulting string "4,5,6" should appear in A1. how can that be done. I can do the concatenation part but dont know about working on selected ranges. Note: The range sould be passed as argument to the sub and the range does not span multiple columns. thx |
#3
Posted to microsoft.public.excel.programming,microsoft.public.office.developer.vba
|
|||
|
|||
subroutine to concatenate strings
One way:
Public Sub Concat(rng As Range) Dim rCell As Range Dim sOut As String For Each rCell In Selection sOut = sOut & "," & rCell.Text Next rCell rng.Value = Mid(sOut, 2) End Sub In article , "MAB" wrote: I want to write a subroutine/macro that will execute on a selected range of cells ( of a single column ). That range will be passed to the subroutine and the sub will concatenate all the values of the cells within the range with commas "," seperating each value. And finally the sub will place the resulting string say in cell A1. for example If I select range A4:A6 which contain values 4,5,6 respectively then when I run the macro/sub the resulting string "4,5,6" should appear in A1. how can that be done. I can do the concatenation part but dont know about working on selected ranges. Note: The range sould be passed as argument to the sub and the range does not span multiple columns. thx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Concatenate strings? | Excel Discussion (Misc queries) | |||
CONCATENATE I have two text strings in cells but it wont work | Excel Discussion (Misc queries) | |||
concatenate strings | Excel Worksheet Functions | |||
space between text strings with concatenate | Excel Discussion (Misc queries) | |||
Concatenate a strings range. | Excel Programming |