Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
Dear friends
i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
=E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and
or text between the cell contents -- paul remove nospam for email addy! "osaka78" wrote: Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
Dear paul
i need macro to do Concatenate regards "paul" wrote: =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and or text between the cell contents -- paul remove nospam for email addy! "osaka78" wrote: Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
Osaka, What Paul gave you was the equivalant of Concatenate just say =E9 & E10 & E11 etc, . . or =E9&E10&E11 etc, . . or =E9 & " " & E10 & " " & E11 etc, . . or =E9&" "&E10&" "&E11 etc to put spaces between the concatenated cells. Do you also have other cells to join? - and if so where are they? -- osaka78 Wrote: Dear paul i need macro to do Concatenate regards "paul" wrote: =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and or text between the cell contents -- paul remove nospam for email addy! "osaka78" wrote: Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531289 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
guys i know this but i need MACRO to do this for me
regards "Bryan Hessey" wrote: Osaka, What Paul gave you was the equivalant of Concatenate just say =E9 & E10 & E11 etc, . . or =E9&E10&E11 etc, . . or =E9 & " " & E10 & " " & E11 etc, . . or =E9&" "&E10&" "&E11 etc to put spaces between the concatenated cells. Do you also have other cells to join? - and if so where are they? -- osaka78 Wrote: Dear paul i need macro to do Concatenate regards "paul" wrote: =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and or text between the cell contents -- paul remove nospam for email addy! "osaka78" wrote: Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531289 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
Try this macro:
Sub osaka78() Dim s As String Dim i As Integer Dim k As Long k = 65536 s = "" For i = 9 To 11 s = s & Cells(i, 5) Next MsgBox (s) Cells(k, 1) = s s = "" For i = 14 To 15 s = s & Cells(i, 5) Next MsgBox (s) Cells(k - 1, 1) = s s = "" For i = 16 To 16 s = s & Cells(i, 5) Next MsgBox (s) Cells(k - 2, 1) = s End Sub It will concatenate the desired cells and place each concatenated group into one cell. -- Gary's Student "osaka78" wrote: guys i know this but i need MACRO to do this for me regards "Bryan Hessey" wrote: Osaka, What Paul gave you was the equivalant of Concatenate just say =E9 & E10 & E11 etc, . . or =E9&E10&E11 etc, . . or =E9 & " " & E10 & " " & E11 etc, . . or =E9&" "&E10&" "&E11 etc to put spaces between the concatenated cells. Do you also have other cells to join? - and if so where are they? -- osaka78 Wrote: Dear paul i need macro to do Concatenate regards "paul" wrote: =E9&E10&E11 or =e9&" "&E10&" "&E11&" and E12 is blank" if you want spaces and or text between the cell contents -- paul remove nospam for email addy! "osaka78" wrote: Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards -- Bryan Hessey ------------------------------------------------------------------------ Bryan Hessey's Profile: http://www.excelforum.com/member.php...o&userid=21059 View this thread: http://www.excelforum.com/showthread...hreadid=531289 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Macro
Function ConcatFormat(rng1 As Range, ParamArray rng2()) As String
Dim i As Long ConcatFormat = rng1.Text For i = LBound(rng2) To UBound(rng2) If Not rng2(i) Is Nothing Then _ ConcatFormat = ConcatFormat & rng2(i).Text Next i End Function -- HTH Bob Phillips (remove nothere from email address if mailing direct) "osaka78" wrote in message ... Dear friends i have a text in cell e9,e10,e11 and e12 is blank then text in e13,e14 and e15 is blank then text in e16 and e17 is blank ..ect how can i create a macro to CONCATENATE all cells before the blanck cell in one cell regards |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Closing File Error | Excel Discussion (Misc queries) | |||
Highlight Range - wrong macro, please edit. | Excel Worksheet Functions | |||
macro with F9 | Excel Discussion (Misc queries) | |||
Make Alignment options under format cells available as shortcut | Excel Discussion (Misc queries) | |||
Playing a macro from another workbook | Excel Discussion (Misc queries) |