I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

The following procedure reads the text in the active cell, adds the requisite
number of rows below it and then adds the labels to these rows. The text in
the active cell must be in the form of a numeric, a space and then text:

Sub MakeLabels()
Dim x As Integer, n As Integer
Dim c As Range
Dim t As String, tt As String

Set c = ActiveCell
c.Value = Trim(c.Value)
n = InStr(c.Value, " ")
t = Left(c.Value, n - 1)
tt = Right(c.Value, Len(c.Value) - n + 1)
If n = 0 Or Not IsNumeric(t) Then Exit Sub
n = CInt(t)
c(2).Resize(n).EntireRow.Insert
For x = 1 To n
c(x + 1) = x & " of " & n & tt
Next
End Sub

Regards,
Greg


"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

Thank you so much Greg!!
Your 1st solution is great because the active cell is being generated
automatically as the user completes an order form on a different worksheet.

Can the code by enhanced for the following?

X apples
Y pears

and so on through a variable number of items?
--
Lele


"Greg Wilson" wrote:

The following procedure reads the text in the active cell, adds the requisite
number of rows below it and then adds the labels to these rows. The text in
the active cell must be in the form of a numeric, a space and then text:

Sub MakeLabels()
Dim x As Integer, n As Integer
Dim c As Range
Dim t As String, tt As String

Set c = ActiveCell
c.Value = Trim(c.Value)
n = InStr(c.Value, " ")
t = Left(c.Value, n - 1)
tt = Right(c.Value, Len(c.Value) - n + 1)
If n = 0 Or Not IsNumeric(t) Then Exit Sub
n = CInt(t)
c(2).Resize(n).EntireRow.Insert
For x = 1 To n
c(x + 1) = x & " of " & n & tt
Next
End Sub

Regards,
Greg


"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

I'm not sure if I understand your situation and if this is appropriate but
the appended code will work for multiple cells containing the item list. For
example:

Cell selection:

5 Apples
4 Pears
3 Grapes
4 Cherries

Output:

5 Apples
1 of 5 Apples
2 of 5 Apples
3 of 5 Apples
4 of 5 Apples
5 of 5 Apples

4 Pears
1 of 4 Pears
2 of 4 Pears
3 of 4 Pears
4 of 4 Pears

3 Grapes
1 of 3 Grapes
2 of 3 Grapes
3 of 3 Grapes

4 Cherries
1 of 4 Cherries
2 of 4 Cherries
3 of 4 Cherries
4 of 4 Cherries

Be advised that I am an amateur programmer only. Minimal testing:

Sub MakeLabels()
Dim i As Integer, n As Integer, x As Integer
Dim r As Range, c As Range
Dim t As String, tt As String

Application.ScreenUpdating = False
Set r = Selection
For i = r.Count To 1 Step -1
r(i) = Trim(r(i))
n = InStr(r(i), " ")
If n 0 Then
t = Left(r(i), n - 1)
If IsNumeric(t) Then
tt = Right(r(i), Len(r(i)) - n + 1)
n = CInt(t)
r(i + 1).Resize(n + 1).EntireRow.Insert
For x = 1 To n
r(i + x) = x & " of " & n & tt
Next
End If
End If
Next
Application.ScreenUpdating = True
End Sub


Regards,
Greg








"Lele" wrote:

Thank you so much Greg!!
Your 1st solution is great because the active cell is being generated
automatically as the user completes an order form on a different worksheet.

Can the code by enhanced for the following?

X apples
Y pears

and so on through a variable number of items?
--
Lele


"Greg Wilson" wrote:

The following procedure reads the text in the active cell, adds the requisite
number of rows below it and then adds the labels to these rows. The text in
the active cell must be in the form of a numeric, a space and then text:

Sub MakeLabels()
Dim x As Integer, n As Integer
Dim c As Range
Dim t As String, tt As String

Set c = ActiveCell
c.Value = Trim(c.Value)
n = InStr(c.Value, " ")
t = Left(c.Value, n - 1)
tt = Right(c.Value, Len(c.Value) - n + 1)
If n = 0 Or Not IsNumeric(t) Then Exit Sub
n = CInt(t)
c(2).Resize(n).EntireRow.Insert
For x = 1 To n
c(x + 1) = x & " of " & n & tt
Next
End Sub

Regards,
Greg


"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

Yes, your understanding of my situation was exactly right. The code is
great! We use Excel to create workorders to produce custom fabric products
one at a time: 16 pillows, 10 bedspreads etc.. 8 pairs of drapes. Once the
product is wrapped in plastic it can be tricky to be sure of the count. We
have a personal label-maker which interfaces with excel and will produce a
label for each row of the workorder spreadsheet. The labels will help us and
our customers know exactly what is being shipped and received. Thanks again!
--
Lele


"Greg Wilson" wrote:

I'm not sure if I understand your situation and if this is appropriate but
the appended code will work for multiple cells containing the item list. For
example:

Cell selection:

5 Apples
4 Pears
3 Grapes
4 Cherries

Output:

5 Apples
1 of 5 Apples
2 of 5 Apples
3 of 5 Apples
4 of 5 Apples
5 of 5 Apples

4 Pears
1 of 4 Pears
2 of 4 Pears
3 of 4 Pears
4 of 4 Pears

3 Grapes
1 of 3 Grapes
2 of 3 Grapes
3 of 3 Grapes

4 Cherries
1 of 4 Cherries
2 of 4 Cherries
3 of 4 Cherries
4 of 4 Cherries

Be advised that I am an amateur programmer only. Minimal testing:

Sub MakeLabels()
Dim i As Integer, n As Integer, x As Integer
Dim r As Range, c As Range
Dim t As String, tt As String

Application.ScreenUpdating = False
Set r = Selection
For i = r.Count To 1 Step -1
r(i) = Trim(r(i))
n = InStr(r(i), " ")
If n 0 Then
t = Left(r(i), n - 1)
If IsNumeric(t) Then
tt = Right(r(i), Len(r(i)) - n + 1)
n = CInt(t)
r(i + 1).Resize(n + 1).EntireRow.Insert
For x = 1 To n
r(i + x) = x & " of " & n & tt
Next
End If
End If
Next
Application.ScreenUpdating = True
End Sub


Regards,
Greg








"Lele" wrote:

Thank you so much Greg!!
Your 1st solution is great because the active cell is being generated
automatically as the user completes an order form on a different worksheet.

Can the code by enhanced for the following?

X apples
Y pears

and so on through a variable number of items?
--
Lele


"Greg Wilson" wrote:

The following procedure reads the text in the active cell, adds the requisite
number of rows below it and then adds the labels to these rows. The text in
the active cell must be in the form of a numeric, a space and then text:

Sub MakeLabels()
Dim x As Integer, n As Integer
Dim c As Range
Dim t As String, tt As String

Set c = ActiveCell
c.Value = Trim(c.Value)
n = InStr(c.Value, " ")
t = Left(c.Value, n - 1)
tt = Right(c.Value, Len(c.Value) - n + 1)
If n = 0 Or Not IsNumeric(t) Then Exit Sub
n = CInt(t)
c(2).Resize(n).EntireRow.Insert
For x = 1 To n
c(x + 1) = x & " of " & n & tt
Next
End Sub

Regards,
Greg


"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

Lele,

Thanks for responding. I appreciate the feedback.

Best regards,
Greg

"Lele" wrote:

Thank you so much Greg!!
Your 1st solution is great because the active cell is being generated
automatically as the user completes an order form on a different worksheet.

Can the code by enhanced for the following?

X apples
Y pears

and so on through a variable number of items?
--
Lele


"Greg Wilson" wrote:

The following procedure reads the text in the active cell, adds the requisite
number of rows below it and then adds the labels to these rows. The text in
the active cell must be in the form of a numeric, a space and then text:

Sub MakeLabels()
Dim x As Integer, n As Integer
Dim c As Range
Dim t As String, tt As String

Set c = ActiveCell
c.Value = Trim(c.Value)
n = InStr(c.Value, " ")
t = Left(c.Value, n - 1)
tt = Right(c.Value, Len(c.Value) - n + 1)
If n = 0 Or Not IsNumeric(t) Then Exit Sub
n = CInt(t)
c(2).Resize(n).EntireRow.Insert
For x = 1 To n
c(x + 1) = x & " of " & n & tt
Next
End Sub

Regards,
Greg


"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele

Of course, as soon as I posted, I realized you don't even need the macro.
Just enter:

"1 of 10 apples"

and drag it down to the required number of labels. The autofill feature will
increment the first number.

Regards,
Greg

"Lele" wrote:

I have very limited programming experience. Is there a simple way to create
code that would read the following row in my spreadsheet:

X apples

and then create the following new rows:
1 of X apples
2 of X apples
and so on.

I am trying to create inventory labels.
--
Lele