Autofill a range

[clares]

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

 

[Dave Hawley]

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

 

[clares]

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?

 

[Guest]

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

 

[Dave Hawley]

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

 

[clares]

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

 

[clares]

This message was edited by clares on 2002-03-15 09:02