~~~ Desparate for Ideas on Excel task 1

[YNOTU]

Hello all, I need some urgent advise on how to approach my issue and was hoping the VBA GODS would help me.

I have an Excel sheet populated from A1 - M1 with the following information (it's from A1-M1 because there are some columns that are hidden and not being used)


QuantityBooks-----NewStore-----Banner-----Address-----att:-----City-----State-----Zip-----Phone

The QuantityBooks fiels contains numbers divisible by 250 with the highest quantity being 32,500 (meaning 130 sets of 250)

The NewStore field contains 3-digit numbers (and only one with 4 numbers being 1000) which are unique and never repeated (there are a total of 203 codes)

For reasons out of our control, We have been asked to ship this data in groups of 250 to each NewStore code. Equaling/totaling the amount found in the QuantityBooks field.
*************************************************************


Example:
If the QuantityBooks field = 250 we should ship all in one box

If the QuantityBooks field = 1,000 we should ship all in four boxes

If the QuantityBooks field = 32,500 we should ship all in 130 boxes



Is this something that can be done with Excel????

thanks in advance all

​

 

[kphu]

I'm not sure exactly what you want done in excel?

 

[YNOTU]

I'm sorry if I was clean.

I was looking to break out sets of 250 based on the Quantity found in the QuantityBooks field... the

​

 

[kphu]

can you post a sample of the data? It'll be easier to understand what your looking for.

Ken

 

[Gavona]

Add a column with the title "boxes"
Insert the formula =a2/250

Copy the formula down to all the rows.

Is that the answer or are you looking for something more?

Thanks,

Gavin

 

[YNOTU]

I don't have the data at home but as soon as I get to work tomorrow I'll post the data... Thanks all

​

 

[YNOTU]

Here's the image and let me clarify what I'm looking to do.

I would like to repeat each record for as many times there are multiples of 250 based on the Quantity books field

IN OTHER WORDS IF THE QUANTATY BOOKS FIELD = 1,500 I SHOULD HAVE THAT RECORDS 6 TIMES SO WHEN I PRINT MY LABELS IT WILL SHOW UP 6 TIMES ON ONE LABEL SHEET I CAN THEN JUST ADD TO EACH LABEL 1 OF 6, 2 OF 6, 3 OF 6 ETC...

Thanks all

​

 

[TonyJollans]

Hi DECEMBER14TH,

Something like this should do it ..

For r = [a65536].End(xlUp).Row To 2 Step -1
er = Cells(r, 1) \ 250 - 1
While er > 0
Rows(r + 1).Insert Shift:=xlDown
Rows(r).Copy Rows(r + 1)
er = er - 1
Wend
Next

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[YNOTU]

Thank you very much Tony, can you tell me where I should apply this code??

​

 

[TonyJollans]

Just drop the code into a procedure in a standard code module and run it. Post back if you need full instructions.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[YNOTU]

eveytime I try to run it from the vb windows the macro name field is blank

​

 

[TonyJollans]

Sorry, a bit more detail. The code I posted is just a fragment - it needs topping and tailing. Here is a better version; you should just be able o cut and paste this directly into a module and run it.

[blue]Sub MultRows()

Dim RowNum As Long
Dim ExtraRowCount As Integer

For RowNum = ActiveSheet.Range("A65536").End(xlUp).Row To 2 Step -1

    ExtraRowCount = ActiveSheet.Cells(RowNum, 1) \ 250 - 1
    
    While ExtraRowCount > 0
        Rows(RowNum + 1).Insert Shift:=xlDown
        Rows(RowNum).Copy Rows(RowNum + 1)
        ExtraRowCount = ExtraRowCount - 1
    Wend

Next

End Sub[/blue]

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at [url=http://www.vbaexpress.

 

[YNOTU]

TY VERY VERY MUCH TONY....


I'm greatful for TEK-TIPS

​