| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Concatenate Loop
Hi!
I have two collums of data CAR BUS 1 TRAIN PLANE 2 MOTORBIKE SCOOTER HELICOPTER 3 TRAM I would like to run a loop which concatenated my data based upon the first collumn (as below) CAR BUS 1 TRAIN CARBUSTRAIN PLANE 2 MOTORBIKE PLANEMOTORBIKE SCOOTER HELICOPTER 3 TRAM SCOOTERHELICOPTERTRAM Thanks in advance Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate Loop
This will need some VBA macro code. Do you mind?
-- AP "FIRSTROUNDKO via OfficeKB.com" <u15639@uwe a écrit dans le message de news:5d0de6d2c58e7@uwe... Hi! I have two collums of data CAR BUS 1 TRAIN PLANE 2 MOTORBIKE SCOOTER HELICOPTER 3 TRAM I would like to run a loop which concatenated my data based upon the first collumn (as below) CAR BUS 1 TRAIN CARBUSTRAIN PLANE 2 MOTORBIKE PLANEMOTORBIKE SCOOTER HELICOPTER 3 TRAM SCOOTERHELICOPTERTRAM Thanks in advance Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate Loop
Ardus,
that would be just great. Darren Ardus Petus wrote: This will need some VBA macro code. Do you mind? -- AP Hi! [quoted text clipped - 24 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate Loop
Paste following code in a Module:
'------------------------------------- Sub concat() 'Cell address of 1st vehicle to be processed Const strFirstVehicle = "B1" 'assume rank is one column to the left 'and result one column to the right Dim rng As Range Dim strResult As String 'Initialize strRsult = "" Set rng = Range(strFirstVehicle) 'Loop Do While rng.Value < "" strResult = strResult & rng.Value If rng.Offset(0, -1).Value < "" Then rng.Offset(0, 1) = strResult strResult = "" End If Set rng = rng.Offset(1, 0) Loop End Sub '---------------------------------------- HTH -- AP "FIRSTROUNDKO via OfficeKB.com" <u15639@uwe a écrit dans le message de news:5d0fe78795c9f@uwe... Ardus, that would be just great. Darren Ardus Petus wrote: This will need some VBA macro code. Do you mind? -- AP Hi! [quoted text clipped - 24 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Concatenate Loop
AP,
Thats just awesome Thanks Darren Ardus Petus wrote: Paste following code in a Module: '------------------------------------- Sub concat() 'Cell address of 1st vehicle to be processed Const strFirstVehicle = "B1" 'assume rank is one column to the left 'and result one column to the right Dim rng As Range Dim strResult As String 'Initialize strRsult = "" Set rng = Range(strFirstVehicle) 'Loop Do While rng.Value < "" strResult = strResult & rng.Value If rng.Offset(0, -1).Value < "" Then rng.Offset(0, 1) = strResult strResult = "" End If Set rng = rng.Offset(1, 0) Loop End Sub '---------------------------------------- HTH -- AP Ardus, [quoted text clipped - 12 lines] Darren -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200603/1 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop gone crazy | Excel Discussion (Misc queries) | |||
| Do Loop | Excel Discussion (Misc queries) | |||
| Using Concatenate inside a vlookup | Excel Worksheet Functions | |||
| VB for excel, how do I loop through code | Excel Discussion (Misc queries) | |||
| Concatenate cells without specifying/writing cell address individually | Excel Discussion (Misc queries) | |||
Linear Mode
