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

H

hanz753

Board Regular
Joined
Aug 9, 2017
Messages
53
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
 
Sort by date Sort by votes
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]
 
Upvote 0

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
Will the 1st line of each "section" always have a value, like


Excel 2013/2016
AB
1COSINVENTORY Warehouse
2INVENTORY Store
3INVENTORY Shops
4INVENTORY Warehouse2
5INVENTORY
6INVENTORY
7TOTAL COS
8SALEBONUS 1
9BONUS 2
10BONUS 3
11BONUS 4
12TOTAL SALES
13ScrapWaste
14Waste
15Waste
16Waste
17Waste
18Total Watse
19AnythingSomething
20Something
21Something
22Total something
Sheet1
 
Upvote 0
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
 
Upvote 0
How about
Code: 
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
 
Upvote 0
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
 
Upvote 0
Try
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Multiple Condition IF/OR Statement with TEXT
Replies
1
Views
983
AlanY
A
H
  • Question Question
Macros - Auto Copy paste row when cell value changes
Replies
7
Views
6K
Fluff
Fluff
C
  • Question Question
copy entire row based on cell vale
Replies
1
Views
696
MARK858
MARK858
B
  • Question Question
Matching Number To Number In text
2 3
Replies
23
Views
2K
Twollaston
T
M
  • Question Question
Modified code not working, as expected
2
Replies
15
Views
2K
Mr2017
M

Forum statistics

Threads
1,223,264
Messages
6,171,085
Members
452,378
Latest member
Hoodzy01

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top