190 views

Skip to first unread message

Aug 21, 2003, 7:09:47 PM8/21/03

to

I have an excel database which contains a list of names of students who

bought school bus passes.

bought school bus passes.

Some of the students bought 10 passes, others 20, some 30, etc.

I have each student listed once in the database, with a field which contains

the number of passes they bought.

How do I set up a merge with Word so that those who bought 10 passes will

get 10 labels, those who bought 20 will get 20, etc.?

Thanks,

Glenn

Aug 22, 2003, 11:09:17 AM8/22/03

to

You might try using a macro to create a temporary list in

excel off your current list providing ten listings of the

student you needs ten, 20, student you needs 20 etc.

excel off your current list providing ten listings of the

student you needs ten, 20, student you needs 20 etc.

as an example

Sub newlist()

Dim rng As Range, rng2 As Range

Dim I As Integer, i2 As Integer, i3 As Integer, c As

Integer

i3 = 1

Set rng = Application.InputBox(Prompt:="Input Range -

Including the number of passes column", Type:=8)

Set rng2 = Application.InputBox(Prompt:="Number of passes

Range", Type:=8)

Sheets.Add

For I = 1 To rng.Rows.Count

For i2 = 1 To Intersect(rng.Rows(I), rng2).Value

For c = 1 To rng.Columns.Count

Cells(i3, c) = rng.Rows(I).Columns(c)

Next c

i3 = i3 + 1

Next i2

Next I

End Sub

>.

>

Aug 22, 2003, 4:08:12 PM8/22/03

to

Hi Glenn,

Another possibility by adding another column to you data for how

many passes you want to print for a row. I assume that it would

be Column 2 (Col B), but you will get a choice of changing the

column. You can use the shorter macro to invoke the larger one

with a specific value which is set up for column 4 as an example.

The number you place in col B or other column of your choice

will be changed to a 1 in the original row and it's replications

on the new sheet.

Everything on the new sheet will be left undisturbed except where

you have a number greater than 1 in your selected column.

So if you have an empty cell in your number column, you will

still present one line to mail merge.

Because the cells with a number in the column

Option Explicit

Sub repeat_BxN_Col4()

repeat_BxN (4)

End Sub

Sub repeat_BxN(Optional ColB As Long)

'Repeat row x Col B value 2003-08-22 in misc

Dim wsSource As Worksheet

Dim wsNew As Worksheet

Set wsSource = ActiveSheet

Dim rng As Range, rng2 As Range

Dim vRows As Long, colBstr As String

Dim I As Long

If ColB = 0 Then

On Error Resume Next

ColB = InputBox("Which column has Repetition Count", _

"Repetition", 2)

If Err.number <> 0 Then Exit Sub

On Error GoTo 0

End If

Sheets(ActiveSheet.Name).Copy _

Before:=Sheets(ActiveSheet.Name)

Set wsNew = ActiveSheet

Application.ScreenUpdating = False

Application.Calculation = xlCalculationManual

Set rng = Cells.Columns(ColB) _

.SpecialCells(xlCellTypeConstants, 1)

Dim A As Long

For A = rng.Areas.Count To 1 Step -1

For I = rng.Areas(A).Count To 1 Step -1

Set rng2 = rng.Areas(A)(I).EntireRow

vRows = rng2.Cells(1, ColB).Value

rng2.Cells(1, ColB).Value = 1

If vRows > 1 Then

rng2.Resize(rowsize:=2).Rows(2).EntireRow. _

Resize(rowsize:=vRows - 1).Insert Shift:=xlDown

rng2.AutoFill rng2.Resize( _

rowsize:=vRows), xlFillCopy

End If

Next I

Next A

Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True

End Sub

If not familiar with macros, directions to install and use on my

Getting Started with Macros

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Page on printing labels with Mail Merge is mailmerg.htm

BTW,

With Lance's example, I had a bit of diffulty figuring out what was being

asked for. But you would enter something like

A3:J4 for first question, essentially for rows 3 through 4

B3:B4 for the second question, essentially to indicate the 2nd column,

for the numbers of rows to repeat for the first range.

i.e. A3:J3 repeated B3 times, and

A4:J4 repeated B4 times

Since I can't remember what I entered first, at leat it might be helpful

to show what the first choice was. Kind of redundant I think you just want the rows

for the first and the column for the second. Or maybe I missed something. The

following would at least tell you what you entered for the first part.

Set rng2 = Application.InputBox(Prompt:="Number of passes Range " _

& Chr(10) & "col 2 of " & rng.Address(0, 0), Type:=8)

One additional comment, Mail Merge requires column labels at the

top of the columns for describing the fields used in Mail Merge.

---

HTH,

David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]

My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm

Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"Lance" <La...@gte.net> wrote in message news:0d7001c368bf$5b14adf0$a401...@phx.gbl...

> You might try using a macro to create a temporary list in

> excel off your current list providing ten listings of the

> student you needs ten, 20, student you needs 20 etc.

>

> as an example

>

> Sub newlist()

> Dim rng As Range, rng2 As Range

> Dim I As Integer, i2 As Integer, i3 As Integer, c As

> Integer

> i3 = 1

> Set rng = Application.InputBox(Prompt:="Input Range -

> Including the number of passes column", Type:=8)

> Set rng2 = Application.InputBox(Prompt:="Number of passes

> Range", Type:=8)

> Sheets.Add

> For I = 1 To rng.Rows.Count

> For i2 = 1 To Intersect(rng.Rows(I), rng2).Value

> For c = 1 To rng.Columns.Count

> Cells(i3, c) = rng.Rows(I).Columns(c)

> Next c

> i3 = i3 + 1

> Next i2

>

> Next I

> End Sub

>

>

>

>

> "Glenn" <gl...@glenncoolong.com> wrote ...

Reply all

Reply to author

Forward

0 new messages

Search

Clear search

Close search

Google apps

Main menu