excel vba cant get autofill or fill down to work

[maverick688]

Hi Everyone I am fairly new to vba. I have a macro that I am trying to input a formula then fill down. I have tried various examples below and i have found a workable answer example 4 works and was done by recording and slightly adjusting the VBA
Each of the other examples also work if I try it on its own, in a standalone macro but when I add them onto the end of a bigger macro the first 3 don't work .
examples 1 and 2 just deletes the header cell in the cell above and doesn't copy down, example 3 copies the header cell in row above and doesn't copy down i have declared the variables the same on them all.
I am just wondering if i am doing something wrong and why they work on their own but not part of the bigger macro.
1)
Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

Range("C2").FormulaR1C1 = "=IF(RC[14]=""294547"",""Fire"",IF(RC[-1]="""","""",""Stock""))"
Range("C2").AutoFill Destination:=Range("C2:C" & LastRow)
2)
Range("C2:C" & LastRow).FormulaR1C1 = "=IF(RC[14]=""294547"",""Fire"",IF(RC[-1]="""","""",""Stock""))"

3)
Range("G2").FormulaR1C1 = "=IF(RC[-4]=""Stock"",""Stock"","""")"
Range("G2:G" & LastRow).FillDown

4)Works but
Range("C2").FormulaR1C1 = "=IF(RC[14]=""294547"",""Fire"",IF(RC[-1]="""","""",""Stock""))"
Range("C2").Copy
Range("D2").End(xlDown).Offset(0, -1).Range("A1").Select
Range(Selection, Selection.End(xlUp)).Select
ActiveSheet.Paste

 

[Joe4]

Welcome to the Board!

If you find the last row you want to apply the formulas to, you do not need to use FillDown - you can apply the formula to the whole range at once, i.e.

Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

Range("C2:C" & LastRow).FormulaR1C1 = "=IF(RC[14]=""294547"",""Fire"",IF(RC[-1]="""","""",""Stock""))"

assuming you can use column D to find the last row you want to go down to.

 

[maverick688]

Welcome to the Board!

If you find the last row you want to apply the formulas to, you do not need to use FillDown - you can apply the formula to the whole range at once, i.e.

Dim LastRow As Long
LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row

Range("C2:C" & LastRow).FormulaR1C1 = "=IF(RC[14]=""294547"",""Fire"",IF(RC[-1]="""","""",""Stock""))"

assuming you can use column D to find the last row you want to go down to.

 

[maverick688]

Thanks Joe, i did try that in my example 2 but it still didnt work.

 

[Joe4]

How exactly did it not work?
What rows did it put formulas in?
What rows SHOULD it put it in?

The most likely issue is that column D is not a good column to use to find the last row with data, maybe we need to use a different column.
Change the reference to "D" here to the column to look at to find the last row with data that we want to copy the formulas down to:

LastRow = ActiveSheet.Cells(Rows.Count, "D").End(xlUp).Row