Auto fill range of cells in MS Excel 2000 1

[lambic]

Hi there,

I was hoping somebody may have some advice on the following:

I want a user to be able to run a batch of mail merge labels in MS Word 2000. The labels contain a sequential numeric code which increments by 1 on each label.
I can achieve this easily emough using Excel as a data source, by defining a cell range that contains the number list.

I would really like to make it easy for them, by having them enter a start & end number in Excel, and have Excel then auto-fill a column with the range of numbers. All they would then have to do is open Word & merge the data.

I'm stuck has how to achieve the auto-fill in Excel, based on a start & end number entered into 2 other cells?

Also, does this sound like a sensible approach?

Many thanks in advance.

 

[kjv1611]

Is this a workbook that you could have a template setup for, or that you could use a user form to take the information, and auto-populate the data?

If you used a form, you could just ask them - in the form: what is the beginning and ending numbers, then press Enter or click a button, and viola, you let the computer do the work - using VBA? You could use a VBA For-Next loop.

I don't know if that is anything like what you would like to do, but it's an idea.

I would think the user form approach would be the cleanest, or either creating a VBA macro, and connecting a button to it, so that the user puts the values in Cells A1 and B1 for instance, clicks the button, and it auto-fills the exact series in column A...

HTH

 

[byundt]

One approach that doesn't require a macro is uses the AutoFill feature of Excel:
1) Enter the first number
2) Enter the second number
3) Select the cells containing the first and second numbers
4) Double-click the little square at the bottom right of the selection marquee. This will AutoFill the serial numbers, incrementing each by 1, until the end of data in the adjacent column.

Brad

 

[byundt]

If you want a macro to create your serial numbers, then try this one. It puts the serial numbers in a column starting with the active cell:

Sub Serialize()
Dim vNumber As Variant, vSerial As Variant
vSerial = InputBox("What is the first serial number?")
vNumber = InputBox("How many serial numbers do you need?")
If vSerial = "" Or vNumber = "" Then Exit Sub
ActiveCell = Val(vSerial)
ActiveCell.Offset(1, 0) = ActiveCell + 1
ActiveCell.Resize(2).AutoFill ActiveCell.Resize(Val(vNumber))
End Sub

Note that the posted macro isn't smart enough to deal with alphameric serial numbers (like AB12345). Please post the pattern if you need something like that.

To install a sub in a regular module sheet:
1) ALT + F11 to open the VBA Editor
2) Use the Insert...Module menu item to create a blank module sheet
3) Paste the suggested code in this module sheet
4) ALT + F11 to return to the spreadsheet

To run a sub or macro:
5) ALT + F8 to open the macro window
6) Select the macro
7) Click the "Run" button

Optional steps to assign a shortcut key to your macro:
8) Repeat steps 5 & 6, then press the "Options" button
9) Enter the character you want to use (Shift + character will have fewer conflicts with existing shortcuts)
10) Enter some descriptive text telling what the macro does in the "Description" field
11) Click the "OK" button

If the above procedure doesn't work, then you need to change your macro security setting. To do so, open the Tools...Macro...Security menu item. Choose Medium, then click OK.

Brad

 

[lambic]

Thanks guys.

I think the macro code would be ideal, the serial number is just a number so should be fine.

I'll give it a go.

Thanks again