VBA Code: Copy Column down to match with text in the next column

[hanz753]

Hello, I am not sure if I can use a formula or a VBA code to do this task.


[TABLE="width: 507"]
<colgroup><col width="64" style="width: 48pt;"> <col width="325" style="width: 244pt; mso-width-source: userset; mso-width-alt: 11885;"> <col width="287" style="width: 215pt; mso-width-source: userset; mso-width-alt: 10496;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]COS
[/TD]
[TD="width: 325, bgcolor: transparent"][/TD]
[TD="width: 287, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Purchase
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Inventorys
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS
[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Inventory
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Inventory
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"] Stock
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]Adjustments
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TOTAL COS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]
I Need the first colum to continue down as "COS" and stop at "TOTAL COS".

Thank you
Hanz

 

[hanz753]

If I were to use different description in column B ( Not Inventor and Bonus)

[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"><tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [TABLE="width: 208"]
<colgroup><col width="95" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;"><colgroup><col width="182" style="width: 137pt; mso-width-source: userset; mso-width-alt: 6656;"><tbody>[TR]
[TD="width: 95, bgcolor: transparent"]COS[/TD]
[TD="width: 182, bgcolor: transparent"]INVENTORY Warehouse[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]INVENTORY Store[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]INVENTORY Shops[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]INVENTORY Warehouse2 [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]INVENTORY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]INVENTORY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]TOTAL COS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALE[/TD]
[TD="bgcolor: transparent"]BONUS 1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]BONUS 2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]BONUS 3[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]BONUS 4[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TOTAL SALES



[/TD]
[/TR]
</tbody>[/TABLE]
Is there a VBA code that would fill from A1 to "TOTAL COS" with COS. And anything above Total sales fills with SALES.
[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Also I would just like to add, this not the full set data. There is more data and criterias.
[/TD]
[TD="bgcolor: transparent"]
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]I thought if know the code to apply for the set I would be able to apply to the rest.

Hanz
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Will the 1st line of each "section" always have a value, like

Excel 2013/2016
	A	B
1	COS	INVENTORY Warehouse
2		INVENTORY Store
3		INVENTORY Shops
4		INVENTORY Warehouse2
5		INVENTORY
6		INVENTORY
7		TOTAL COS
8	SALE	BONUS 1
9		BONUS 2
10		BONUS 3
11		BONUS 4
12		TOTAL SALES
13	Scrap	Waste
14		Waste
15		Waste
16		Waste
17		Waste
18		Total Watse
19	Anything	Something
20		Something
21		Something
22		Total something
Sheet1

 

[hanz753]

Sorry for the confusion,

In Column A, I need the rows from A2 to "TOTAL REVENUE" ( TOTAL REVENUE is in column "C") to be filled with "REVENUE".

Below that, I would need the Cells to be filled with "COS" until "Cost of Sales" ( Cost of Sales is in column C)

This would contiue for Gross Profit, Finance Cost, Service, Admin and Income.

Thank you for your help

Hanz

 

[hanz753]

No, I would have to enter manually.

 

[hanz753]

Sorry thinking about, Yes each line will contain the value.

 

[Fluff]

How about

Sub FillColA()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("B1", Range("B" & Rows.Count).End(xlUp))
      .Replace "total", "=xxxTotal", xlPart, , False, , False, False
      Set Ar = .SpecialCells(xlConstants).Areas
      .Replace "=xxxTotal", "Total", xlPart, , False, , False, False
   End With
   For Each Rng In Ar
      With Rng.Offset(Rng.Count).Resize(1, 1)
      Rng.Offset(, -1).Value = Mid(.Value, InStr(1, .Value, " ") + 1)
      End With
   Next Rng
End Sub

 

[hanz753]

It doesn't seem to be working. Having a long hard think about this I would need to enter the text in manually.

Sorry for the confusion, I really appreciate your help.

In Column A, I need the rows from A2 to "TOTAL REVENUE" ( TOTAL REVENUE is in column "C") to be filled with "REVENUE".

Below that, I would need the Cells to be filled with "COS" until "Cost of Sales" ( Cost of Sales is in column C)

This would contiue for Gross Profit, Finance Cost, Service, Admin and Income.

Thank you for your help

Hanz

 

[Fluff]

Try

Sub FillColA()
   Dim Rng As Range
   Dim Ar As Areas
   With Range("C2", Range("C" & Rows.Count).End(xlUp))
      .Replace "total", "=xxxTotal", xlPart, , False, , False, False
      Set Ar = .SpecialCells(xlConstants).Areas
      .Replace "=xxxTotal", "Total", xlPart, , False, , False, False
   End With
   For Each Rng In Ar
      With Rng.Offset(Rng.Count).Resize(1, 1)
      Rng.Offset(, -2).Resize(Rng.Count + 1).Value = Mid(.Value, InStr(1, .Value, " ") + 1)
      End With
   Next Rng
End Sub