Advice/Suggestion for solving a problem (Macro)

[clares]

I received some advice and can not find the response and didn't keep a copy. My problem is that I want to print labels from word by importing from excel as I currently export a great deal from our database into excel. What I would like to be able to do is have a macro that will automate the following task. I have tried but have spent many hours getting nowhere!!

The user needs to be able to enter a value (the amount of labels that need to be printed) and using a vlookup, select the product that needs the labels, which is stored in another worksheet. The problem is that i need to be able to store the same product details in the same amount of rows as defined by the user, hence the input value. This then when imported in word will give me for example 10 labels that have the same information on it.
I have solved this to some degree however, the problem I have is that I am unable to code the macro to start the process again with a another product (b) starting directly under the previous product (a). The difficulty is that each time a product is entered they may contain different values (product a require 2 labels, product b requires 15 labels) therefore I can't directly define a range of cells. Is there anyone that can assist me PLEASE?

 

[NateO]

Perhaps I'm not reading the issue correctly, but if you copy 15 entries and just want to paste the entries at the end of a column's worth of data, you try something like the following to select the appropriate cell:

sub ClSel()
    If [a1] = "" Then
    [a1].Select
    Exit Sub
    End If
    If [a2]<> "" Then
    [a1].End(xlDown).Offset(1, 0).Select
    Else: [a2].Select
    End If
End Sub

Hope this helps.

_________________
Cheers,<font size=+2><font color="red"> Nate<font color="blue">O</font></font></font>
This message was edited by NateO on 2002-03-17 14:59

 

[Abdc]

Found your previous posting by doing a search of your name ;_
http://www.mrexcel.com/board/viewtopic.php?topic=2417&forum=2

It is not entirely clear what it is you want to do. Which of the previous responses is closest?

Perhaps this is what you need ;-

Dim nbr As Integer
If Selection.Cells.Count <> 1 Or _
Selection.Column <> 1 Or _
Selection.Address <> [A65536].End(xlUp).Address Then
MsgBox "You must select the last cell only in Column A."
Exit Sub
End If
nbr = Application.InputBox("Enter how many labels are required", Type:=1)
If nbr = False Then Exit Sub
Range(Selection, Selection(nbr, 1)).Value = Selection.Value

 

[clares]

Brilliant - I have tried this and seems to work, will adapt to format sheet but thank you for you help.