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.

COS
COS		Purchase
COS		Inventorys
COS		Inventory
		Inventory
		Stock
		Adjustments
		TOTAL COS

<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> </tbody>

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)

COS INVENTORY Warehouse COS INVENTORY Store COS INVENTORY Shops COS INVENTORY Warehouse2 COS INVENTORY COS INVENTORY COS TOTAL COS SALE BONUS 1 BONUS 2 BONUS 3 BONUS 4 TOTAL SALES <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> </tbody> Is there a VBA code that would fill from A1 to "TOTAL COS" with COS. And anything above Total sales fills with SALES.
Also I would just like to add, this not the full set data. There is more data and criterias.
I thought if know the code to apply for the set I would be able to apply to the rest. Hanz

<colgroup><col width="64" style="width: 48pt;" span="2"><tbody>
</tbody>

 

[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