Fill with a formula until Cell is Blank

  • Thread starter Thread starter John
  • Start date Start date
J

John

What code would I use to copy a formula down until Column A.. is blank. I
can copy etc but don't know what code to use that identifies that Column A
is blank


Thanks
 
Range("C1").Copy
x = 2
Do Until Cells(x, 1).Value = ""
Cells(x, 3).PasteSpecial xlPasteFormulas
x = x + 1
Loop

- Piku
 
I'm still having a problem, it hits debug on my second line i..e
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,3,0)*$E2"




Sub Format_Query()


Sheets("Sales Mix").Select


Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,3,0)*$E2"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,4,0)*$E2"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,5,0)*$E2"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,6,0)*$E2"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,7,0)*$E2"


Range("F2.J2").Copy
x = 2
Do Until Cells(x, 1).Value = ""
Cells(x, 3).PasteSpecial xlPasteFormulas
x = x + 1
Loop


Range("A1").Select

End Sub
 
use ActiveCell.Formula =
instead of
ActiveCell.FormulaR1C1 =

John said:
I'm still having a problem, it hits debug on my second line i..e
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,3,0)*$E2"




Sub Format_Query()


Sheets("Sales Mix").Select


Range("F2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,3,0)*$E2"
Range("G2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,4,0)*$E2"
Range("H2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,5,0)*$E2"
Range("I2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,6,0)*$E2"
Range("J2").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP($C2,Master_Menu_Item_Nos,7,0)*$E2"


Range("F2.J2").Copy
x = 2
Do Until Cells(x, 1).Value = ""
Cells(x, 3).PasteSpecial xlPasteFormulas
x = x + 1
Loop


Range("A1").Select

End Sub
Click to expand...
 
Thanks for the reply, I just recorded the macro and used that recorded code


Cecilkumara Fernando said:
use ActiveCell.Formula =
instead of
ActiveCell.FormulaR1C1 =
Click to expand...
 
Back
Top