PDA

View Full Version : [SOLVED] Merge cells with alive data & seprators



ilyaskazi
03-21-2005, 03:23 AM
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
03-21-2005, 06:58 AM
can somebody help me....: pray2: : pray2: : pray2:

Tinku
03-21-2005, 08:49 AM
Hi.. I am a newbie too but have you tried concatenate()..

I think it shuld work for you.

Regards

Tinku

Anne Troy
03-21-2005, 10:50 AM
Try this:
http://www.tushar-mehta.com/excel/software/text_write_program/text_write_program.zip (http://www.tushar-mehta.com/excel/software/text_write_program/text_write_program.zip)

See sheet2!

mdmackillop
03-21-2005, 12:09 PM
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
03-21-2005, 08:29 PM
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
03-21-2005, 10:03 PM
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
03-22-2005, 01:25 AM
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
03-22-2005, 01:36 AM
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
03-22-2005, 05:14 AM
Output cell is user defined in sheet2. Output must come by clicking the shortcut button for the selection of rows from sheet1.

ilyaskazi
03-22-2005, 11:31 PM
help..

Zack Barresse
03-23-2005, 10:16 AM
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
03-23-2005, 08:12 PM
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
03-24-2005, 11:17 AM
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
03-24-2005, 12:06 PM
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
03-24-2005, 03:15 PM
Thanks Zack, please do that. The focus goes to the command button, and all I get is a beep

Zack Barresse
03-24-2005, 03:25 PM
Okay, here ya go. Let me know if that is about what you were trying, or if I'm in left field ..

ilyaskazi
03-24-2005, 08:28 PM
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
03-25-2005, 01:27 AM
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
03-25-2005, 07:58 AM
u r genius :thumb

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