Macro Help: Reproducing Value in cell

[Ytime]

Hey Guys,

Ok so here a simple explanation of my problem.

I have a list of stores in each store i have a number value which reprisents how many cartons of a product have been sold into the store. Lets say the store name is in column A and the QTY of cartons sold in is in column B.
I need a macro that will take each store look for the QTY of cartons they sold in and reproduce the store name on a different sheet in one column.

so Store CL Warnumbull, QTY 5
becomes

CL Warnumbull
CL Warnumbull
CL Warnumbull
CL Warnumbull
CL Warnumbull

Thanks for your help

 

[Michael M]

Hi and Welcome to the Board.
Do you really need macro ??
You could do a sort ascending by column B and then copy the rows in column A to your new sheet.
Having said that, you question simply requires ALL names in column A, which doesn't make much sense to me.
Maybe try to be a bit more specific, eg, what criteria in column B do you need to find a store name in A

Regards
Michael M

 

[Ytime]

Sorry i should of provided a bit more info!

Currently i am running a sales drive for a string of products in stores with incentives for cartons sold in. However its going to be a raffle for example 5 cartons sold in = 1 raffle.

The data comes out as

Visit Date,Store,Product,QTY Sold in
and then i have manually added "Raffle"(Formula that takes the number of tickets achieved from the QTY to determine how many times i want this particular store to appear)

The idea is if someone has got a value of 2 for the raffle in a store i would like to reproduce the store twice.

with this giant list of stores i would of used the randbetween forumla to randomly select a winner.

 

[Michael M]

Hmm, that's an interesting way to do it.
I'm afraid my VBA isn't as good as most, but I'm sure there will be some simple lines of code provided from other users.
I think you will need a do While loop....but we'll see.
Regards
Michael M.

 

[Michael M]

*bump*

 

[Ytime]

Bump

any solutions out there?

 

[Michael M]

Ytime
I did some searching and found this code.

Sub Repeat()
Dim rng As Range
Dim NoRows As Long
    Set rng = Range("A1")
    While rng.Value <> ""
        NoRows = rng.Offset(, 1) - 1
        rng.Offset(1).Resize(NoRows).EntireRow.Insert
        rng.Offset(1).Resize(NoRows) = rng.Value
        Set rng = rng.Offset(NoRows + 1)
    Wend
End Sub

Which does as you require, BUT, I'd suggest you copy your original sheet to a backup sheet and run it from there.
The reason being it creates the new entries back into column A
If I find an improvement I'll let you know.
Hope this helps
Regards
Michael M