Place sub category's letters in all items

Panoos64

Well-known Member
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​

<tbody>
</tbody>

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​

<tbody>
</tbody>

Last edited:

Fluff

MrExcel MVP, Moderator
How do you know if something should be FRUI or BAKE?

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Panoos64

Well-known Member
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

MrExcel MVP, Moderator
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

MrExcel MVP
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

Well-known Member

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

Well-known Member
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

Well-known Member
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

Well-known Member
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!

Replies
1
Views
355
Replies
1
Views
423
Replies
6
Views
406
Replies
3
Views
674
Replies
21
Views
1K

1,129,666
Messages
5,637,649
Members
416,979
Latest member
CapeCon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back