Yet Another Macro Question

[Materialised]

As so often happens, my manager has now decided he would like to do it
another way. (Going Back to my BMX example)

Colour Codes ProdID Desc Price
2374 BMX Bicycle £99.99

A new row has been inserted, where the user will type the various colour
codes into. So for example:

Colour Codes ProdID Desc Price
W,R,B 2374 BMX Bicycle £99.99

The user would then select this row, and run a macro, and would have:
Colour Codes ProdID Desc Price
W,R,B 2374/W BMX Bicycle £99.99
W,R,B 2374/R BMX Bicycle £99.99
W,R,B 2374/B BMX Bicycle £99.99

Anyone have any ideas?
To be totally honest, I am no windows programmer, Im a UNIX admin, and
its been over 10 years since I even made a ripple in the water with any
windows programming. So any advise or pointers you could give me would
great.

Thanks
Mick

 

[Bernie Deitrick]

Mick,

Assumimg the colour codes are in column A:

Sub TryNow()
Dim myCell As Range
Dim myR As Range
Dim myCodes As Variant
Dim i As Integer

Set myR = Selection.Cells(1).EntireRow
Set myCell = myR.Cells(1, 1)

myCodes = Split(myCell.Value, ",")

If LBound(myCodes) <> UBound(myCodes) Then
myR.Copy
myR.Resize(UBound(myCodes) - LBound(myCodes)).Offset(1).Insert

For i = LBound(myCodes) To UBound(myCodes)
myCell(i + 1, 2).Value = myCell(i + 1, 2).Value & "/" & myCodes(i)
Next i
End If

End Sub

HTH,
Bernie
MS Excel MVP