VBA To copy specific rows many time depending on Europena Postcode data in each row and filter data from cells.

[jamesworger]

Hi,

Background: I have a list of shipping prices that depend on; Country; Postcode; Weight. Many postcodes have the same price but are not in number sequence, for example postcodes 5000-7999 will be the same cost as postcodes 11000-12999. Our website CMS system, Magento, requires all shipping costs to be in postcode order, thus I need to upload a list showing the price for 5000-7999 cost, then 8000-10999 cost, then 11000-12999 costs.

I have added below two sample worksheets:

• Master - this is the list I currently have with the correct prices, Col.B shows all postcode bands that particular price covers (each separated by a colon)
• List - this is what I want the VBA formula to produce, a list of all prices, but with only 1 postcode band per line.

Notes:

• Postcodes are seen as text on our website, thus we need to put 1-4999 and 0001-499 as a postcode, in case a customer puts a postcode with or without the 0's before.
• Ideally, 1 button would be pressed and, from the Master worksheet, the List worksheet is created.

Is this possible and if so, how would you suggest I go about this and can you offer any advice?

Master worksheet:
[TABLE="width: 850"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Postcode[/TD]
[TD]Weight[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]5000-5999;05001-05999;45000-45999;49000-49999;56000-57999;59000-59999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
</tbody>[/TABLE]


List Worksheet:
[TABLE="width: 539"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Postcode[/TD]
[TD="align: right"]Weight[/TD]
[TD="align: right"]Cost[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]1-4999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]30.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]58.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]73.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]88.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]5000-5999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]05001-05999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]45000-45999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]49000-49999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]56000-57999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]31.00[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]59.65[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]74.60[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]59000-59999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]89.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]1-4999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]00001-04999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]50001-55999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]60001-61999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.30[/TD]
[TD="align: right"]33.00[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.50[/TD]
[TD="align: right"]61.65[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]0.75[/TD]
[TD="align: right"]76.60[/TD]
[/TR]
[TR]
[TD]FIN[/TD]
[TD]28000-66999[/TD]
[TD="align: right"]1.00[/TD]
[TD="align: right"]91.55[/TD]
[/TR]
</tbody>[/TABLE]

 

[MickG]

Try this Results start "G1".
NB:- I had to add "," to each PostCode , because some of them , when split reverted to dates.

[COLOR=Navy]Sub[/COLOR] MG26Feb53
[COLOR=Navy]Dim[/COLOR] Rng         [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] Dn          [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] n           [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]
[COLOR=Navy]Dim[/COLOR] Dic         [COLOR=Navy]As[/COLOR] Object
[COLOR=Navy]Dim[/COLOR] Q           [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] Ray()
[COLOR=Navy]Dim[/COLOR] oTxt        [COLOR=Navy]As[/COLOR] [COLOR=Navy]String[/COLOR]
[COLOR=Navy]Set[/COLOR] Rng = Range(Range("A2"), Range("A" & Rows.Count).End(xlUp))
[COLOR=Navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare


[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] Dn [COLOR=Navy]In[/COLOR] Rng
    oTxt = Dn.value & Dn.Offset(, 1).value
    [COLOR=Navy]If[/COLOR] Not Dic.Exists(oTxt) [COLOR=Navy]Then[/COLOR]
        Dic.Add oTxt, Array(Dn.Offset(, 1), Dn.Offset(, 2))
    [COLOR=Navy]Else[/COLOR]
        Q = Dic.Item(oTxt)
        [COLOR=Navy]Set[/COLOR] Q(1) = Union(Q(1), Dn.Offset(, 2))
        Dic.Item(oTxt) = Q
    [COLOR=Navy]End[/COLOR] If
[COLOR=Navy]Next[/COLOR]
[COLOR=Navy]Dim[/COLOR] K
[COLOR=Navy]Dim[/COLOR] Sp [COLOR=Navy]As[/COLOR] Variant
[COLOR=Navy]Dim[/COLOR] P [COLOR=Navy]As[/COLOR] Range
[COLOR=Navy]Dim[/COLOR] c [COLOR=Navy]As[/COLOR] [COLOR=Navy]Long[/COLOR]


[COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] K [COLOR=Navy]In[/COLOR] Dic.keys
    Sp = Split(Dic.Item(K)(0), ";")
        [COLOR=Navy]For[/COLOR] n = 0 To UBound(Sp)
            
            [COLOR=Navy]For[/COLOR] [COLOR=Navy]Each[/COLOR] P [COLOR=Navy]In[/COLOR] Dic.Item(K)(1)
                c = c + 1
                ReDim Preserve Ray(1 To 4, 1 To c)
                Ray(1, c) = Dic.Item(K)(0).Offset(, -1)
                Ray(2, c) = Sp(n) & ","
                Ray(3, c) = P
                Ray(4, c) = P.Offset(, 1)
            [COLOR=Navy]Next[/COLOR] P
        [COLOR=Navy]Next[/COLOR] n
[COLOR=Navy]Next[/COLOR] K
[COLOR=Navy]With[/COLOR] Range("G1")
    .Resize(, 4).value = Array("Country", "Postcode", "Weight", "Cost")
    .Offset(1).Resize(c, 4) = Application.Transpose(Ray)
[COLOR=Navy]End[/COLOR] [COLOR=Navy]With[/COLOR]
[COLOR=Navy]End[/COLOR] [COLOR=Navy]Sub[/COLOR]

Regards Mick

 

[BrianMH]

Mick instead of adding a "," after the postcode you could add a single quote (') before it. That way when the value is placed in the cell it won't be part of the value but it will know to treat it as text.

 

[jamesworger]

MickG,

That is brilliant.

All the new postcode bands include a "," at the end, is it possible to remove this and set as a text cell (so excel does not change, for example 1-4999, into a date)?


Thanks,

James

 

[BrianMH]

James I believe if you change this line:

Ray(2, c) = Sp(n) & ","

to this:

"'" & Ray(2, c) = Sp(n)

you will get what you need.

 

[BrianMH]

'

 

[MickG]

Good idea Brian , but like this perhaps

Ray(2, c) = "'" & Sp(n)

 

[BrianMH]

LOL. Yeah that's what I get for working on stuff here while waiting for reports to run at work. Get rushed and miss the detail. Nice catch.

 

[MickG]

Good Idea Though !!!!

 

[jamesworger]

MickG,

How can I change the VBA to accomodate a new column between Country and Postcode. It will be called Region and simply contain an asterisk.

Example:
[TABLE="width: 811"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Country[/TD]
[TD]Region[/TD]
[TD]Postcode[/TD]
[TD]Weight[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]POL[/TD]
[TD]*[/TD]
[TD]1-4999;00001-04999;50001-55999;60001-61999[/TD]
[TD]0.3[/TD]
[TD]30[/TD]
[/TR]
</tbody>[/TABLE]