Fill Cells with value from another Cell with condition of empty Cell next to

[parsec]

How can I take the vale of a cell "BULK" and inserted in the cells of the column DEPT, but only up to the next value "FROZEN" then fill the next cell with the value "FROZEN" up to the next value "DAIRY & REFRIGERATED" and so on. Only common thinG in this is that the cell next to the values that I like to copy in the column DEPT are always empty
Thank you
ITEM# | UPC # | BRAND NAME | DEPT (it should look like this)
BULK
027493-6 | 0-26938-27493-1 | BULK PEAS AND BEANS ORGANIC | BULK
056858-4 | 0-26938-56858-0 | BULK PEAS AND BEANS ORGANIC | BULK
073655-3 | 0-26938-73655-2 | BULK SEEDS | BULK
065366-7 | 0-26938-65366-8 | BULK SEEDS | BULK
FROZEN
152447-9 | 8-56952-00160-8 | SMART FLOUR | FROZEN
019468-8 | 0-89947-30206-4 | VAN'S NATURAL FOODS | FROZEN
152390-1 | 8-51209-00408-1 | WINK FROZEN DESSERTS | FROZEN
DAIRY & REFRIGERATED
174706-2 | 3-40224-00696-1 | CHIA POD | DAIRY & REFRIGERATED
196836-1 | 8-18290-01020-9 | CHOBANI | DAIRY & REFRIGERATED
151256-5 | 8-94700-01004-5 | CHOBANI | DAIRY & REFRIGERATED

 

[MickG]

Try:-

[COLOR="Navy"]Sub[/COLOR] MG12Dec50
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp)).SpecialCells(xlCellTypeConstants)
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng.Areas
    Dn.Offset(, 2).Value = Dn(1).Offset(-1, -1).Value
[COLOR="Navy"]Next[/COLOR] Dn
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[parsec]

Thank you sir
But out of my limited knowledge on the code above it has not produce any results.
Can you please if possible provide more detail on using the code
Thank you

 

[MickG]

To Save and Run Code:-
Copy code from Thread
In Your Data sheet , Click "Alt+F11",:- Vb Window appears.
From the VBWindow toolbar, Click "Insert" ,"Module":- New VBwindow appears .
Paste Code into this window.
Close Vbwindow.
On sheet Click "Developer tab", Click "Macro". Macro dialog box appears.
Select Macro (with same name) from List.
On the right of Dialog box Click "Run"
The Sheet should now be updated.
Regrds Mick


Below is your data with the yellow cells representing what it looks like after the code has run.
Is the layout of your data any different?????

	A	B	C	D
1	ITEM#	UPC #	BRAND NAME	DEPT (it should look like this)
2	BULK
3	027493-6	0-26938-27493-1	BULK PEAS AND BEANS ORGANIC	BULK
4	056858-4	0-26938-56858-0	BULK PEAS AND BEANS ORGANIC	BULK
5	073655-3	0-26938-73655-2	BULK SEEDS	BULK
6	065366-7	0-26938-65366-8	BULK SEEDS	BULK
7	FROZEN
8	152447-9	8-56952-00160-8	SMART FLOUR	FROZEN
9	019468-8	0-89947-30206-4	VAN'S NATURAL FOODS	FROZEN
10	152390-1	8-51209-00408-1	WINK FROZEN DESSERTS	FROZEN
11	DAIRY & REFRIGERATED
12	174706-2	3-40224-00696-1	CHIA POD	DAIRY & REFRIGERATED
13	196836-1	8-18290-01020-9	CHOBANI	DAIRY & REFRIGERATED
14	151256-5	8-94700-01004-5	CHOBANI	DAIRY & REFRIGERATED

<tbody>
</tbody>

 

[parsec]

thank you thank you

I wish that I was able to understand the logic on this code
thank you so much

 

[MickG]

You're welcome

 

[parsec]

Hi
How I can change the code to insert the Values into another column instead in column D
thanks

 

[MickG]

The Code looks at column "B" and the line below shows offset(,2), That "D", change the number for the 2 for the offset columns you require.

[B][COLOR=#FF0000]Dn.Offset(, 2).Value [/COLOR][/B][COLOR=#FF0000][/COLOR]= Dn(1).Offset(-1, -1).Value