Yet Another Macro Question

  • Thread starter Thread starter Materialised
  • Start date Start date
M

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
 
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
 
Back
Top