Place sub category's letters in all items

Panoos64 · Oct 15, 2019

All hi, Kindly require to provide me a support so that a VBA code to run through col. “G” and where is missing the sub category, should place the 4 letters on left of dash in each item. However, the subcategory which consists with 4 initial letters exists in some of the items.
Therefore, that the spreadsheet’s row are 15000+. Please see below an extract of original date and expected result. Thank you all in advance

Original data

	F	G
13	FOOD	-Royal Gala Apples x 6
14	FOOD	-Kiwi Fruit x 6
15	FOOD	-Red Grapes 600gr
16	FOOD	FRUI-Oranges x 5
17	FOOD	FRUI-Raspberries 100gr
18	Fruits Total	FRUITS
19	FOOD	-Sliced Wholemeal 1000gr
20	FOOD	-White Rolls x 24
21	FOOD	-Penne Pasta 800 gr
22	FOOD	-White Baguettes x 3
23	FOOD	BAKE-Spaghetti 500gr
24	Bakeries & Grains Total	BAKERIES & GRAINS

Expected result

	F	G
13	FOOD	FRUI-Royal Gala Apples x 6
14	FOOD	FRUI-Kiwi Fruit x 6
15	FOOD	FRUI-Red Grapes 600gr
16	FOOD	FRUI-Oranges x 5
17	FOOD	FRUI-Raspberries 100gr
18	Fruits Total	FRUITS
19	FOOD	BAKE-Sliced Wholemeal 1000gr
20	FOOD	BAKE-White Rolls x 24
21	FOOD	BAKE-Penne Pasta 800 gr
22	FOOD	BAKE-White Baguettes x 3
23	FOOD	BAKE-Spaghetti 500gr
24	Bakeries & Grains Total	BAKERIES & GRAINS

Fluff · Oct 15, 2019

How do you know if something should be FRUI or BAKE?

Panoos64 · Oct 15, 2019

Hi Fluff, the "Sub Category" "FRUI", "BAKE" e.t.c. is valid through stock control system whenever i export the report "Inventory Items". Therefore that the system valid this information from date e.g. 10/08/2019 i required from programmer to appear such information but in old items from the mentioned date and back it was impossible to valid it. This is the reason that i require to complete the codes, "FRUI", "BAKE" e.t.c of each item for old items too so that to able to prepare specific statistics.

Fluff · Oct 15, 2019

That does not answer my question. In your op it had the sub category below each set, now it doesn't.
So how do you determine what should be added to the front of those rows that start with a -?

mikerickson · Oct 15, 2019

change this line

Code:

prefix = UCase(Left(.Offset(-1, 1).Value, 4))

(and also change the .Cell(j,3) 's to .Cells(j,2). But it sounds like you've done that already.)

Akuini · Oct 15, 2019

Maybe this is what you want:

Code:

[FONT=Lucida Console][COLOR=Royalblue]Sub[/COLOR] a1112401a()

[COLOR=Royalblue]Dim[/COLOR] i [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], j [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR], k [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]Long[/COLOR]
[COLOR=Royalblue]Dim[/COLOR] va
[COLOR=Royalblue]Dim[/COLOR] pref [COLOR=Royalblue]As[/COLOR] [COLOR=Royalblue]String[/COLOR]

va = Range([COLOR=Darkcyan]"G13"[/COLOR], Cells(Rows.count, [COLOR=Darkcyan]"G"[/COLOR]).[COLOR=Royalblue]End[/COLOR](xlUp))

[COLOR=Royalblue]For[/COLOR] i = [COLOR=Brown]1[/COLOR] [COLOR=Royalblue]To[/COLOR] [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR])
    j = i
    pref = [COLOR=Darkcyan]""[/COLOR]
    
    [COLOR=Royalblue]Do[/COLOR]
        [COLOR=Royalblue]If[/COLOR] pref = [COLOR=Darkcyan]""[/COLOR] [COLOR=Royalblue]And[/COLOR] Mid(va(i, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]5[/COLOR], [COLOR=Brown]1[/COLOR]) = [COLOR=Darkcyan]"-"[/COLOR] [COLOR=Royalblue]Then[/COLOR] pref = Left(va(i, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]4[/COLOR])
        i = i + [COLOR=Brown]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] i > [COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR]) [COLOR=Royalblue]Then[/COLOR] [COLOR=Royalblue]Exit[/COLOR] [COLOR=Royalblue]Do[/COLOR]
    [COLOR=Royalblue]Loop[/COLOR] [COLOR=Royalblue]While[/COLOR] Trim(va(i, [COLOR=Brown]1[/COLOR])) <> [COLOR=Darkcyan]"[COLOR=Royalblue]Sub[/COLOR] Category Items"[/COLOR]
    
    [COLOR=Royalblue]For[/COLOR] k = j [COLOR=Royalblue]To[/COLOR] i - [COLOR=Brown]1[/COLOR]
        [COLOR=Royalblue]If[/COLOR] Left(va(k, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]1[/COLOR]) = [COLOR=Darkcyan]"-"[/COLOR] [COLOR=Royalblue]Then[/COLOR] va(k, [COLOR=Brown]1[/COLOR]) = pref & va(k, [COLOR=Brown]1[/COLOR])
    [COLOR=Royalblue]Next[/COLOR]
    
[COLOR=Royalblue]Next[/COLOR]

Range([COLOR=Darkcyan]"G13"[/COLOR]).Resize([COLOR=Royalblue]UBound[/COLOR](va, [COLOR=Brown]1[/COLOR]), [COLOR=Brown]1[/COLOR]) = va
[COLOR=Royalblue]End[/COLOR] [COLOR=Royalblue]Sub[/COLOR][/FONT]

Panoos64 · Oct 16, 2019

Well done Akuini! Thank you so much for preparation of the command. it works perfect now. Thank yu once again for your support and your time. Hv a great day!

Panoos64 · Oct 16, 2019

Hi mike, i changed it but it doesn't work. What i was attempting to change in your first post it was the starting row which was "13". The code was running and was placing the correct data from col. "G1". I know that is complicate code and do not worry. I just wrote for feedback purposes. Thank you so much for your time. Hv a lovely day

Panoos64 · Oct 16, 2019

Hi Fluff, Just for each Sub Category the code or formula should place the 4 digits in each item's line. Therefore that the 4 letters exist in some lines and the formula or code should run through col. "G" and should place in those lines which doesn't exist. Taking into consideration that some rows description start with dash "-", therefore that the code should copying the 4 letters from an items which has the code and should place it on left of the dash. However do not worry and thank you for your continued support and i appreciate it that, many times you provide me support. I just wrote for feedback purposes. Hv a great day!

Place sub category's letters in all items

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

Excel Facts

Panoos64

Well-known Member

Fluff

MrExcel MVP, Moderator

mikerickson

MrExcel MVP

Akuini

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Panoos64

Well-known Member

Similar threads

Forum statistics

Share this page

Place sub category's letters in all items

Well-known Member

MrExcel MVP, Moderator

Excel Facts

Well-known Member

MrExcel MVP, Moderator

MrExcel MVP

Well-known Member

Well-known Member

Well-known Member

Well-known Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock