Fill with a formula until Cell is Blank

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
 
P

pikus

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

- Piku
 
J

John

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
 
C

Cecilkumara Fernando

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

John

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Fill in the blanks 1
Fill/Copy Up to fill blank cells. 3
Continue formula until reaching a blank cell. 5
Eliminate blanks while copy row and transpose 11
Issue with blanks and spaces 56
I want to make One Cell mandatory if another cell has a specific value , can anyone help me? 0
Macro to Copy and Paste Cell Contents 2
Create if partial match then copy and paste data from one cell to another 1

Top