Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Autofill a range

  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'm sure there must be someone that can help me. I posted a previous note but felt I needed to be more specific. My question is that I want to select a product from a range that I have exported from another system and I want to be able to print a set of labels, for example sometimes I want to print 5 labels containing the same text and other times I want to print 24 say. That I have no problem with. My problem is that I want to be able, by using a macro, to fill a range (row) with the same data. It's this that is used for exporting into word for labels i.e.
    row1= AA4567 Pens
    row2= AA4567 Pens
    row3= AA4567 Pens
    In the above example I will get three labels, but only want to enter data in row1, the macro will copy to the amount that I ask it to!

    Can anyone suggest any ideas! Will be much appreciated

  2. #2
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi clares

    No too surte I have understood you but here 3 ways, 2 are that are static the other 1 is incremeting.



    Sub DoThePenThing1()
    Sheet1.Range("A2:A500") = "AA4567"
    End Sub

    Sub DoThePenThing2()
    Sheet1.Range("A2:A500") = Sheet1.Range("A1")
    End Sub

    Sub DoThePenThing3()
    Sheet1.Range("A2").AutoFill Destination:=Sheet1.Range("A2", Sheet1.Range("A500"))
    End Sub

    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training


    [ This Message was edited by: Dave Hawley on 2002-03-15 03:36 ]

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

    Default

    Thanks for that Dave, but using the last option I think will fill the range between A2 and A500 with the data I want, but sometimes I only want to fill to A4, how can I allow it to accept an input from the user?, then automactically fill the range?

    Does that make sense?

  4. #4
    Guest

    Default

    If Ivan's suggestions are not what you're looking for, perhaps this will help.

    I have assumed that your imported product number occupies one cell only. If this is not the case, post again.

    In any event, try the following. First select a cell containing the product number required and then run the code. It should enter the product number in the number of rows in column A specified by the input box.

    Dim nbr As Integer
    If Selection.Cells.Count <> 1 Then
    MsgBox "You must select one cell only"
    Exit Sub
    End If
    nbr = Application.InputBox("Enter how many labels are required", Type:=1)
    If nbr = False Then Exit Sub
    Columns(1).ClearContents
    Range("A1:A" & nbr) = Selection

  5. #5
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Sure can, try this

    Sub DoiT()
    Dim rStopRange As Range
    On Error Resume Next
    Set rStopRange = Application.InputBox(Prompt:="Select the cell to stop at", Type:=8)

    If rStopRange Is Nothing Then End
    Range("A2").AutoFill Destination:=Range("A2", rStopRange)
    End
    End Sub

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

    Default

    These suggestions are really helpful. Thank you all for the responses. I do beleive I am able to make progress. If I have further queries as the small project moves on, I will post

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

    Default




    [ This Message was edited by: clares on 2002-03-15 09:02 ]

Some videos you may like

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
  •