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

 

[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:

COS	INVENTORY
COS	INVENTORY
COS	INVENTORY
COS	INVENTORY
COS	INVENTORY
COS	INVENTORY
COS	TOTAL COS
SALES	BONUS
SALES	BONUS
	BONUS
	BONUS
	TOTALSALE

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

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:

#REF!

<colgroup><col></colgroup><tbody>
</tbody>

 

[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.