Merge cells with alive data & seprators

[ilyaskazi]

I want to merge cells with alive data and seprator as colon ";"

See Attachments..

Output should be as given in Blue box...

ilyas kazi.

[ilyaskazi]

can somebody help me....

[Tinku]

Hi.. I am a newbie too but have you tried concatenate()..

I think it shuld work for you.

Regards

Tinku

[Anne Troy]

Try this:
http://www.tushar-mehta.com/excel/software/text_write_program/text_write_program.zip

See sheet2!

[mdmackillop]

How about a simple UserDefinedFunction (UDF), Paste the following code in a standard module and enter =DoMerge(A1:A4) in a cell on your worksheet. See the yellow cells in the attachment.

Function DoMerge(Target As Range)
Dim mrg As String, cel
mrg = ";"
For Each cel In Target
mrg = mrg & cel & "; "
Next cel
DoMerge = mrg
End Function

[ilyaskazi]

hi mdmackillop,

Ur solution is good and seems to be working fine only with the workbook which u provided. I need this code to be written in the Personal.xls book (excel startup file).

I hv tried this entering the domerge formula but no output came.

help..

[Zack Barresse]

Hi,

If the UDF is already in you Personal.xls file, then you can still call it, just make sure you type in where it's located ...

=Personal.xls!DoMerge(A1)

.. change out A1 for your reference.

[ilyaskazi]

thanku firefytr, its working... Good.

I m calling this through vba now for which i hv created shortcut in toolbar. For this shortcut now I need to put formula for active cell in which output must come.

How to get selected ranges (i.e.data to manipulate) and to put formula for the output cell (i.e.active cell)???

[mdmackillop]

We can make code to work on any selected range, but where is the output cell in relation to say, the first cell in the range? Next column, Above, somewhere else entirely? If this is not "fixed" in a relative sense, a slightly different approach is required.

[ilyaskazi]

Output cell is user defined in sheet2. Output must come by clicking the shortcut button for the selection of rows from sheet1.

[ilyaskazi]

help..

[Zack Barresse]

ilyaskazi,

You need to be a little more descriptive. Calling a Function from a Procedure will need more information. The way you are describing things, there is not enough information for this. Can you post an example file (zipped) which shows an example of what you mean?

From what I understand, you have a toolbar shortcut button that you want to click and have this formula put for a selected range. Yes? Please make sure (in any example file) that you show the relationship between the affected range being computed and the location of the cell(s) with the UDF.

[ilyaskazi]

Yes firefytr, u r correct. I hv made a toolbar shortcut btn.

Clicking the button, it calls..

Sub MergeOrigin()
Dim FsCel As String
Dim LsCel As String
Sheets("FARES").Select
sRange = Selection.Rows.Count
FsCel = ActiveCell.Row 
If sRange > FsCel Then
LsCel = sRange + FsCel - 1
Else
LsCel = FsCel - sRange + 1
End If
'MsgBox "Total row selection = " & sRange
'MsgBox "First selected Cell = " & FsCel
'MsgBox "Last selected Cell = " & LsCel
Sheets("CONDITIONS").Select 
If sRange = 1 Then
ActiveCell.value = "=PERSONAL.xls!DoMerge(FARES!D" + FsCel & ")"
Else
ActiveCell.value = "=PERSONAL.xls!DoMerge(FARES!D" + FsCel & "" + LsCel & ")"
End If
'ActiveCell.Copy
'ActiveCell.PasteSpecial Paste:=xlPasteValues
End Sub

I hv made the code in such if the user select the ranges even in descending order, the output must be in ascending order.

But it is not working correctly. If user select the range (ie. D5:20) output is null. This is not the solution. plz help.

Below is the function...

Function DoMerge(Target As Range)
Dim mrg As String, cel
mrg = ";"
For Each cel In Target
mrg = mrg & cel & ";"
Next cel
DoMerge = mrg
End Function

I hv attached an example file for ur reference..

Regards,
ilyas kazi

[mdmackillop]

Hi Zack,
It seemed to me a way forward to use a userform with two RefEdit controls and a button to run the Function macro. I can enter the ranges in the RefEdits but get no response at all from the button. (The form is stored in Personal.xls) Any ideas?

[Zack Barresse]

Huh, works for me. With a userform and the code of ...

Option Explicit

Private Sub CommandButton1_Click()
    'Cancel button
    Unload Me
End Sub

Private Sub CommandButton2_Click()
    'Ok button
    With Me.RefEdit1
        Range(.Value).Formula = "=Personal.xls!DoMerge(" & Me.RefEdit2.Value & ")"
    End With
    Unload Me
End Sub

the first RefEdit box referring to where I wanted the formula located, the second referring to the range of the DoMerge. I could upload a sample Personal.xls if you'd like..

[mdmackillop]

Thanks Zack, please do that. The focus goes to the command button, and all I get is a beep

[Zack Barresse]

Okay, here ya go. Let me know if that is about what you were trying, or if I'm in left field ..

[ilyaskazi]

hi Zack,

Your userform is working good. But 4 me it will waste my time to show the path for manipulating. While clicking my shortcut button (from toolbar) it should automatically detect the path from sheet1 of selected cell(s) and must give the output in Sheet2 to active cell. Also it should handle if user has selected cells in descending order in Sheet1 to show output in ascending order only.

awaiting....

[mdmackillop]

Hi,
I accept the blame for the userform thought. Anyway, what you need is very much simpler.

Sub MergeOrigin()
Dim sRange As Range
Set sRange = Selection
Sheets("Conditions").Activate
ActiveCell.Value = "=PERSONAL.xls!DoMerge(Fares!" & sRange.Address & ")"
End Sub

[ilyaskazi]

u r genius

finally it is solved.....thank u..