VBA: Auto fill formulas down a column

[sam82]

I have amounts down column D, and quantities of that amount down column C. In column G, I'd like my macro to insert a formula that multiplies the two amounts together for each row to come up with a total.

I was thinking that I could probably create a Do While and Loop macro, but is there an easier way?

Thanks,

 

[RSXchin]

this is just to show you how it could work. Change what you need. as for the formula, pretend the formula would be EXACTLY what you would put in the first row.

Sub down_the_columns()
    Range("c1:c50").Formula = "=a1+b1" ' you can make this be whatever.  even the lastrow of A if you prefer.
End Sub

 

[sam82]

this is just to show you how it could work. Change what you need. as for the formula, pretend the formula would be EXACTLY what you would put in the first row.

Sub down_the_columns()
    Range("c1:c50").Formula = "=a1+b1" ' you can make this be whatever.  even the lastrow of A if you prefer.
End Sub

Just a quick follow up question, how would I make the macro stop at the last populated row? It doesn't always end at row 200; sometimes it goes to 210 or beyond, sometimes stops at 190.

 

[RSXchin]

which column goes the farthest?

 

[sam82]

which column goes the farthest?

Column G, the one that will contain the formulas.

I'd like the macro to stop creating formulas once it reaches that last row that has an amount and quantity in it.

Usually I would just have it go down to say 500, but I saw a method where you can make the macro stop once it reaches the last populated row.

 

[RSXchin]

column G is empty then... i'll just assume I should use column A as a reference

range("G1:G" & cells(rows.count, "A").end(xlup).row).formula = "=a1+b1"

 

[sam82]

column G is empty then... i'll just assume I should use column A as a reference

range("G1:G" & cells(rows.count, "A").end(xlup).row).formula = "=a1+b1"

Wow, that would have taken me almost ten lines to do.

It worked just fine, thanks!

I think I understand all that's happening in that code, but I'm not sure what the "A" is for. Could you explain that part?

 

[RSXchin]

you'll use a lot of that kind of code
this is used to show the lastrow in column B

msgbox cells(rows.count, "B").end(xlup).row

and this is to show how many columns are in row 5

msgbox cells(5, columns.count).end(xltoleft).columns