VBA- A macro which Applies Formula down a column until last row

[DesperateKid]

Hi
I need a macro for VBA which applies a formula From cell I4 to the last I value for which Column A has data filled in.
The formula in the worksheet would be equivalent to "= $j3+1" In Cell I4 which is then dragged down(autofilled) until the last row in column I.

Thanks so much!!

 

[Fluff]

Try

Range("A2").Formula = "=VLOOKUP(C[3],'pcode'!C:C[1],2,0)"
Range("A2", "A" & Cells(Rows.Count, 4).End(xlUp).Offset(, -3).Row).Filldown

 

[harveya915]

Sub FillFormula()
Range("I4").Formula = "=$J3+1"
Range("I4", "I" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
End Sub

I've been looking for something like this. However when I insert it in to my code i run it, it only appears on one cell, then it dissapears from that cell. This is the whole code that I have. (I'm a beginner). What I want to accomplish is for when the columns are added, the fill formula runs and it is added from column c4 all the way down to the last item in column A. I would also like to add a different formula for column G. Any help would be gladly appreciated.

Private Sub UserForm1_Initialize()
End Sub


Private Sub InsertNewWeek_Click()

Dim AddColumns()
'Inserts Columns at c-h
Worksheets(1).Range("c:h").EntireColumn.Insert
Worksheets(1).Range("c3").Formula = "End Week Total"
Worksheets(1).Range("d3").Formula = "Used"
Worksheets(1).Range("e3").Formula = "Restocked"
Worksheets(1).Range("f3").Formula = "Price Per Item"
Worksheets(1).Range("g3").Formula = "Purchase Total"
Worksheets(1).Range("h3").Formula = ""

Unload UserForm1

End Sub

Sub FillFormula()
Range("C4").Formula = "=I4-D4+E4"
Range("C4", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

End Sub
I

 

[JoeMo]

I've been looking for something like this. However when I insert it in to my code i run it, it only appears on one cell, then it dissapears from that cell. This is the whole code that I have. (I'm a beginner). What I want to accomplish is for when the columns are added, the fill formula runs and it is added from column c4 all the way down to the last item in column A. I would also like to add a different formula for column G. Any help would be gladly appreciated.

Private Sub UserForm1_Initialize()
End Sub


Private Sub InsertNewWeek_Click()

Dim AddColumns()
'Inserts Columns at c-h
Worksheets(1).Range("c:h").EntireColumn.Insert
Worksheets(1).Range("c3").Formula = "End Week Total"
Worksheets(1).Range("d3").Formula = "Used"
Worksheets(1).Range("e3").Formula = "Restocked"
Worksheets(1).Range("f3").Formula = "Price Per Item"
Worksheets(1).Range("g3").Formula = "Purchase Total"
Worksheets(1).Range("h3").Formula = ""

Unload UserForm1

End Sub

[B]Sub FillFormula()
Range("C4").Formula = "=I4-D4+E4"
Range("C4", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

End Sub[/B]

Assuming you are referring to the code shown in bold font (above), are you running the code while the sheet you want to enter the formula on and fill down to the last row in col A is the active sheet? If not, you need to qualify the sheet in each range and Cells reference, otherwise it will execute your commands on the active sheet which may give you unexpected results.

 

[harveya915]

@JoeMo Thanks for your reply. I don't quite understand what you're saying. From what I gather from it, you're saying to Activate the sheet? Sheet1.Activate?
And to answer your question, yes, i'm running the code on the sheet that I want it on is the active sheet.

I went through a bunch of formulas that i've gathered throughout projects and websites and this is what I came up with, and it seems to be working so far.

Dim FillFormula()
Range("C4").Formula = "=I4-D4+E4"
Range("C4", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Range("G4").Formula = "=E4*F4"
Range("G4", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Do you see anything that might give me a problem later on?

 

[JoeMo]

@JoeMo Thanks for your reply. I don't quite understand what you're saying. From what I gather from it, you're saying to Activate the sheet? Sheet1.Activate?
And to answer your question, yes, i'm running the code on the sheet that I want it on is the active sheet.

I went through a bunch of formulas that i've gathered throughout projects and websites and this is what I came up with, and it seems to be working so far.

Dim FillFormula()
Range("C4").Formula = "=I4-D4+E4"
Range("C4", "C" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown
Range("G4").Formula = "=E4*F4"
Range("G4", "G" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown

Do you see anything that might give me a problem later on?

I don't understand the FillFormula() variable you've dimensioned as an array - what do you intend to do with it?. But, no matter, the fill down part should work fine so long as the active worksheet is the one you want to fill the formulas on when you run the code.

 

[harveya915]

Well, i just copied your code from this thread. Since i have no experience with vba, i just googled a bit more and took a look at other pieces of code that i have acquired from other projects. I played around with the code until ot worked. So far.

 

[Schwyl]

Well This thread is still helping people like me. Thank you for this

 

[JoeMo]

Well This thread is still helping people like me. Thank you for this

You are welcome - thanks for the reply.