Advice/Suggestion for solving a problem (Macro)
Manage your personal finances in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Advice/Suggestion for solving a problem (Macro)

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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:

    Code:
    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, NateO

    [ This Message was edited by: NateO on 2002-03-17 14:59 ]

  3. #3

    Join Date
    Mar 2002
    Posts
    20
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Found your previous posting by doing a search of your name ;_
    http://www.mrexcel.com/board/viewtop...c=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

  4. #4
    Board Regular
    Join Date
    Mar 2002
    Location
    Portsmouth.UK
    Posts
    557
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com