PDA

View Full Version : Solved: Complex code needed (Seperate words)



av8tordude
06-08-2011, 01:59 PM
I need a code that will look into each cell in column B and find each city separated by “/” and separate each city (along with the rest of the information) into its own row. (See Attached)



For example…


California | Antioch / Brentwood / Concord | Contra Costa | $1.00 | $2.00 | $3.00
California | Antioch| Contra Costa | $1.00 | $2.00 | $3.00
California | Brentwood| Contra Costa | $1.00 | $2.00 | $3.00
California | Concord | Contra Costa | $1.00 | $2.00 | $3.00

p45cal
06-08-2011, 02:45 PM
try:
Sub spreadOut()
lastRow = Cells(Rows.count, 1).End(xlUp).Row
For rw = lastRow To 5 Step -1
With Cells(rw, 2)
myArray = Split(.Value, "/")
If UBound(myArray) > 0 Then
Cells(rw, 2).Value = Application.Trim(myArray(UBound(myArray)))
For i = 0 To UBound(myArray) - 1
Rows(rw).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Rows(rw + 1).Copy Rows(rw)
Cells(rw, 2).Value = Application.Trim(myArray(i))
Next i
End If
End With
Next rw
End Sub
Assumes list starts at row 5

av8tordude
06-08-2011, 03:42 PM
AWESOME!!! Thank you p45cal :bow::beerchug: