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 |
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 |
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 |
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. |
CONCATENATE on separated values
The error that it is showing is
Run-time error '5': Invalid procedure call or argument |
All times are GMT +1. The time now is 02:21 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com