Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
CONCATENATE on separated values
I would like to know how I can take different cells (non concurrent)
and string them together in another cell with separators. The CONCATENATE function can't do this. The end result that I am looking for is to select a number of cells using a userform and listbox with checkboxes, and have all of those values combine and be pasted into a cell of my choosing. I have the userform, the listbox, and the checkboxes (courtesy of John Walkenbach's book), I need to figure out how to combine my selections into a single cell. Thanks for your reponses in advance. Shane |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
CONCATENATE on separated values
Shane
don't bother with CONCATENATE. Just use, for example: =A1 & "/" & B3 & "/" & C5 & ... Regards Trevor wrote in message oups.com... I would like to know how I can take different cells (non concurrent) and string them together in another cell with separators. The CONCATENATE function can't do this. The end result that I am looking for is to select a number of cells using a userform and listbox with checkboxes, and have all of those values combine and be pasted into a cell of my choosing. I have the userform, the listbox, and the checkboxes (courtesy of John Walkenbach's book), I need to figure out how to combine my selections into a single cell. Thanks for your reponses in advance. Shane |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
CONCATENATE on separated values
The CONCATENATE function can add separators between cell references
=CONCATENATE(D2,",",C10,",",C14,",",E12) Or a macro which allows you to select non-contiguous cells and entert the separator you want. Sub ConCat_Cells() Dim x As Range Dim y As Range Dim z As Range Dim w As String Dim sbuf As String On Error GoTo endit w = InputBox("Enter the Type of De-limiter Desired") Set z = Application.InputBox("Select Destination Cell", _ "Destination Cell", , , , , , 8) Application.SendKeys "+{F8}" Set x = Application.InputBox("Select Cells...Contiguous or Non-Contiguous", _ "Cells Selection", , , , , , 8) For Each y In x If Len(y.text) 0 Then sbuf = sbuf & y.text & w Next z = Left(sbuf, Len(sbuf) - 1) Exit Sub endit: MsgBox "Nothing Selected. Please try again." End Sub Gord Dibben MS Excel MVP On Wed, 15 Aug 2007 14:02:20 -0700, wrote: I would like to know how I can take different cells (non concurrent) and string them together in another cell with separators. The CONCATENATE function can't do this. The end result that I am looking for is to select a number of cells using a userform and listbox with checkboxes, and have all of those values combine and be pasted into a cell of my choosing. I have the userform, the listbox, and the checkboxes (courtesy of John Walkenbach's book), I need to figure out how to combine my selections into a single cell. Thanks for your reponses in advance. Shane |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
CONCATENATE on separated values
I think that I can merge your macro with my userform to get what I
need. Here is what I have. I know that I have variables that I don't need. Private Sub OK_exp_Click() Dim CellRange As Range Dim CellCnt As Integer Dim r As Integer Dim x As Range Dim w As String Dim y As Range Dim z As Range Dim sbuf As String w = ", " CellCnt = 0 For r = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(r) Then CellCnt = CellCnt + 1 If CellCnt = 1 Then Set CellRange = ActiveSheet.UsedRange.Cells(r + 1) Else Set CellRange = Union(CellRange, ActiveSheet.UsedRange.Cells(r + 1)) End If End If Next r For Each y In CellRange If Len(y.Text) 0 Then sbuf = sbuf & y.Text & w Next z = Left(sbuf, Len(sbuf) - 1) Unload Me End Sub Right now it is showing me the error on the z= left... line. I think that my address for pasting the code into a cell is wrong. Can you help me. |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
CONCATENATE on separated values
The error that it is showing is
Run-time error '5': Invalid procedure call or argument |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to I create a comma separated values file | Excel Discussion (Misc queries) | |||
How do I convert a column of numbers into comma separated values . | Excel Worksheet Functions | |||
How to get values separated with / | Excel Discussion (Misc queries) | |||
Fixing Comma Separated Values (.csv) | Excel Discussion (Misc queries) | |||
Concatenate columns, separated by double quotes | Excel Worksheet Functions |