Hi All,

I have a very large sheet, which covers a suppliers range of products.
The format is as follows:
ALDL/A/R Glass/Nkl 75 25.51 51.02 £51.02

Where the first column is the product code, the second relates to the
colours of the product available.
What I need to do, is loop through the sheet, and create individual rows
for each available product.
So instead of the above, I would have something like:
ALDL/A/R-1 Glass 75 25.51 51.02 £51.02
ALDL/A/R-2 Nkl 75 25.51 51.02 £51.02
The product code has now changes, to reflect an individual product.
I am very new to excel programming and have only covered the simplest of
things, but the way I see it working is the user would specify a column
which in this would be B to indicated which column the various colour
codes are in. Then they would have to specify a seperator character
which in this case would be "/" (these are not always the same) then the
macro would loop through the sheet, making the alerations.
Could anyone give me a few pointers on how I could do this please, as I
said before, I am not to good with writing macros.

Thanks

(Sorry for the repost, I didnt want any title confusion with a previous
post)

Hi there

You could try something like this which may help you out

First open up VBE from the tools menu or Alt + F11

Insert a userform then on that user form add

1 x commandbutton
2 x label
2 x textbox

Place them anywhere on the form and leave the name details as
defaulted

Then paste the code below to the code module for the userform

Option Explicit 'declare your variables here
Dim MyCol, MySep As String
Dim LstCell, MyCell, MyRng As Range
Dim MyArr
Dim FullCnt, i, X As Integer
Private Sub CommandButton1_Click()

MyCol = TextBox1.Value 'pass textbox1 to a string
MySep = TextBox2.Value 'pass textbox1 to a string

If MyCol = "" Then 'if no column entered stop the sub
MsgBox "Please declare a column"
Exit Sub
End If
If MySep = "" Then 'if no separator entered stop the sub
MsgBox "Please declare a Separator"
Exit Sub
End If

LstCell = Range(MyCol & 1).End(xlDown).Address 'find the last used
cell in column
Set MyRng = Range(MyCol & 1, LstCell) 'set the range of cells to
use

For Each MyCell In MyRng
i = 0
X = 1
MyArr = Split(MyCell, "/", -1) 'split the contents of the
cell _
holding the colours and pass each value to an array
FullCnt = UBound(MyArr) - LBound(MyArr) + 1 'count elements in
the array
Sheets(2).Activate 'move to another sheet
[A65536].End(xlUp).Offset(1, 0).Select 'go to the first blank cell in
column a _
leaving the first row clear for headers

Do While X <= FullCnt 'start a loop
ActiveCell = MyCell.Offset(0, -1) & "-" & X 'add 'place values in
cells
ActiveCell.Offset(0, 1) = MyArr(i)
ActiveCell.Offset(0, 2) = MyCell.Offset(0, 1)
i = i + 1 'increament by 1
X = X + 1 'increament by 1
ActiveCell.Offset(1, 0).Select 'move to next empty cell
Loop
Next MyCell 'move to next cell holding colours
End Sub
Private Sub UserForm_Initialize()
Sheets(1).Activate 'Change this to the worksheet you require to run
the code from _
or if you add the module below to load the form you can delete this
line
With Me
..Height = 141
..Width = 255
End With
With Label1
..Height = 9.75
..Left = 24
..Top = 12
..Width = 216
..Caption = "Please enter the column letter containing the colours."
End With
With TextBox1
..Height = 18
..Left = 24
..Top = 30
..Width = 72
End With
With Label2
..Height = 12
..Left = 24
..Top = 54
..Width = 216
..Caption = "Please enter the separator character."
End With
With TextBox2
..Height = 18
..Left = 24
..Top = 72
..Width = 72
End With
With CommandButton1
..Left = 162
..Top = 78
..Caption = "Continue"
End With
End Sub

If you want to run the code from the form you could modify it to suit
the worksheet module or you could insert a module and paste the
following code into it

Sub Loader()
UserForm1.Show vbModal
End Sub

Then from the main excel screen open the Macro applet from the Tools
menu or Alt + F8 click on the options button and enter a shortcut key
to run the macro from. This will load the form

Hope this helps you out

Steve

Let me know if this helps.

Sub changeproductcodes()
For i = Cells(Rows.Count, "d").End(xlUp).Row To 14 Step -1
Rows(i - 1).Insert
c = Cells(i, "d")
p1 = InStr(c, " ")
p2 = InStr(p1, c, "/")
p3 = InStr(p2, c, " ")
s1 = Left(c, p1 - 1) & "-1" & Mid(c, p1, p2 - p1) & Right(c, Len(c) - p3 +
1)
s2 = Left(c, p1 - 1) & "-2 " & Mid(c, p2 + 1, p3 - p2) & Right(c, Len(c) -
p3 + 1)
Cells(i, "d").Value = s1
Cells(i + 1, "d").Value = s2
Next i
End Sub

--
Don Guillett
SalesAid Software

"Materialised" wrote in message
...
Hi All,

I have a very large sheet, which covers a suppliers range of products.
The format is as follows:
ALDL/A/R Glass/Nkl 75 25.51 51.02 £51.02

Where the first column is the product code, the second relates to the
colours of the product available.
What I need to do, is loop through the sheet, and create individual rows
for each available product.
So instead of the above, I would have something like:
ALDL/A/R-1 Glass 75 25.51 51.02 £51.02
ALDL/A/R-2 Nkl 75 25.51 51.02 £51.02
The product code has now changes, to reflect an individual product.
I am very new to excel programming and have only covered the simplest of
things, but the way I see it working is the user would specify a column
which in this would be B to indicated which column the various colour
codes are in. Then they would have to specify a seperator character
which in this case would be "/" (these are not always the same) then the
macro would loop through the sheet, making the alerations.
Could anyone give me a few pointers on how I could do this please, as I
said before, I am not to good with writing macros.

Thanks

(Sorry for the repost, I didnt want any title confusion with a previous
post)