----------------------------------------------------------------------------------------------------------------------------
Hi Mehdi,
Try this code: (Changes highlighted in Red)
[code]
Dim Rng As Range, Dn As Range, oVal, oSp As Long
Dim Ray, c As Long
Set Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
ReDim Ray(1 To Rng.Count * 4, 1 To 4)
For Each Dn In Rng
oVal = Split(Dn.Offset(, 3), "/")
If UBound(oVal) > 0 Then
For oSp = 0 To UBound(oVal)
c = c + 1
Ray(c, 1) = Dn
Ray(c, 2) = Dn.Offset(0,1)
Ray(c, 3) = Dn.Offset(0,2)
Ray(c, 4) = oVal(oSp)
Next oSp
Else
c = c + 1
Ray(c, 1) = Dn
Ray(c, 2) = Dn.Offset(, 1)
Ray(c, 3) = Dn.Offset(, 2)
Ray(c, 4) = Dn.Offset(, 3)
End If
Next Dn
Range("C2").Resize(c, 4).Value = Ray
[/code]
For more tips, tricks, downloads and problem solutions: visit -->
http://www.excelitems.com
------------------------------------------------------------------------
Regards
*Ashish **Jain*
Analyst and Demand-Co-ordinator, CSC Australia
Microsoft Certified Application (Excel) Specialist
Technical Trainer - Excel and VBA
Author -> www.excelitems.com
09999-40-48-43
----------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------
On Mon, Mar 2, 2009 at 7:58 PM, Mehdi_21 <meh.i...@gmail.com> wrote:
>
> hey guys
>
> i have a problem decomposing cells, i'm using a table like this one :
>
> A B
> 1 name1 | S1/S2
> 2 name2 | M1/M2
> .
> .
> n namen | D1/D2
>
> i want it to look like this:
>
> A B
> 1 name1 | S1
> 2 name1| S2
> 3 name2 | M1
> 4 name2 |M2
> .
> .
> n namen | D1
> n+1 namen |D2
>
> thanks in advance for ur help
> regards
>
> >
--~--~---------~--~----~------------~-------~--~----~
-------------------------------------------------------------------------------------
Some important links for excel users:
1. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
http://www.excelitems.com
2. Excel tutorials at http://www.excel-macros.blogspot.com
3. Learn VBA Macros at http://www.vbamacros.blogspot.com
4. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
If you find any spam message in the group, please send an email to:
Ayush Jain @ jainayus...@gmail.com or
Ashish Jain @ 26may.1...@gmail.com
-------------------------------------------------------------------------------------
-~----------~----~----~----~------~----~------~--~---
