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

 

[My Aswer Is This]

Try this:

Assuming we are dealing with column A and B

Sub Fill_Down()
'Modified 8/8/2018 5:55 AM  EDT
Application.ScreenUpdating = False
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
Range("A1").Resize(Lastrow).Value = "COS"
Application.ScreenUpdating = True
End Sub

 

[hanz753]

Thank you for responding to my email.

I forgot to meniton in my previous email, once "COS" is in line with "TOTAL COS". The second criteria would be to match "Sales" with "Total Sales".

 

[My Aswer Is This]

I do not understand:
You said:

match "Sales" with "Total Sales"​

 

[hanz753]

sorry for not being clear. Please see examle below:


[TABLE="width: 96"]
<colgroup><col width="64" style="width: 48pt;" span="2"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"]COS[/TD]
[TD="width: 64, 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"]INVENTORY[/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"]INVENTORY[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]COS[/TD]
[TD="bgcolor: transparent"]TOTAL COS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALES[/TD]
[TD="bgcolor: transparent"]BONUS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]SALES[/TD]
[TD="bgcolor: transparent"]BONUS
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]BONUS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]BONUS[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]TOTALSALE
[/TD]
[/TR]
</tbody>[/TABLE]

Once "COS" is in line with "TOTAL COS". The next step is to have "SALES" line with "TOTALSALE".

Thanks
Hanz

 

[My Aswer Is This]

Try this:

Sub Copy_Range()
'Modified 8/8/2018 8:20 AM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Lastrow = Cells(Rows.Count, "B").End(xlUp).Row
For i = 1 To Lastrow
If Cells(i, 2).Value = "INVENTORY" Then Cells(i, 2).Offset(, -1).Value = "COS"
If Cells(i, 2).Value = "TOTAL COS" Then Cells(i, 2).Offset(, -1).Value = "COS"
If Cells(i, 2).Value = "BONUS" Then Cells(i, 2).Offset(, -1).Value = "SALES"
If Cells(i, 2).Value = "TOTALSALE" Then Cells(i, 2).Offset(, -1).Value = "SALES"
Next
Application.ScreenUpdating = True
End Sub

 

[Fluff]

Another option

Sub FillBlanks()
With Range("A:A")
   .SpecialCells(xlBlanks).Formula = "=r[-1]c"
   .Value = .Value
End With
End Sub

 

[My Aswer Is This]

Another option

Sub FillBlanks()
With Range("A:A")
   .SpecialCells(xlBlanks).Formula = "=r[-1]c"
   .Value = .Value
End With
End Sub

When I try this script all I get in column A is:
[TABLE="width: 150"]
<colgroup><col></colgroup><tbody>[TR]
[TD="align: center"]#REF![/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Did you have a value in A1?

 

[My Aswer Is This]

Did you have a value in A1?

When I put COS in Range("A1")
It fills all 1.5 million rows in column A with COS

I really never like using script I do not understand.
I have no ideal what:
.SpecialCells(xlBlanks).Formula = "=r[-1]c"
.Value = .Value

I like using Excel Vba simple scripting language.