Autofill to the Bottom of the existing Data

[Eaglboy1]

Hey guys. I have one more for the experts. I'm trying to create a macro that will find a location, input an equation, and autofill that equation to the bottom of the existing data. I tried recording the macro and using the double click feature, but when I was done, it didn't create a variable to the bottom of the existing data. I'm hoping someone can help me edit this code so that it will work the way I'm hoping. Here's what I have so far:

Cells.Find(What:="cents off", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 4).Range("A1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "=RC[-4]&""OFF"""
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A325")
    ActiveCell.Range("A1:A325").Select
End Sub

Let me know if you can help and I appreciate what help you can offer.

Thanks everyone!

 

[netuser]

Hey guys. I have one more for the experts. I'm trying to create a macro that will find a location, input an equation, and autofill that equation to the bottom of the existing data. I tried recording the macro and using the double click feature, but when I was done, it didn't create a variable to the bottom of the existing data. I'm hoping someone can help me edit this code so that it will work the way I'm hoping. Here's what I have so far:

Cells.Find(What:="cents off", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.Offset(1, 4).Range("A1").Select
    Selection.NumberFormat = "General"
    ActiveCell.FormulaR1C1 = "=RC[-4]&""OFF"""
    ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A325")
    ActiveCell.Range("A1:A325").Select
End Sub

Let me know if you can help and I appreciate what help you can offer.

Thanks everyone!

Can you clarify what you want to do ?

Search for "cents off" and then put formula in 4th Column from it and then copy that formula till end of the sheet (non empty rows)?

 

[Eaglboy1]

That is correct. The entire top section of my macro works great. The only portion I'm having issues with is this portion

ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A325")
    ActiveCell.Range("A1:A325").Select

As of right now, it only autofills down 325 rows. I'm hoping to find a way to get it to autofill to the bottom of whatever existing data there is.

 

[netuser]

That is correct. The entire top section of my macro works great. The only portion I'm having issues with is this portion

ActiveCell.Select
    Selection.AutoFill Destination:=ActiveCell.Range("A1:A325")
    ActiveCell.Range("A1:A325").Select

As of right now, it only autofills down 325 rows. I'm hoping to find a way to get it to autofill to the bottom of whatever existing data there is.

Here you go,

Dim LastRow, ActiveRow, ActiveCol

LastRow = Range("A" & Rows.Count).End(xlUp).Row

Cells.Find(What:="cents off", After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    
    
ActiveRow = ActiveCell.Row
ActiveCol = ActiveCell.Column + 4

    With Range(Cells(ActiveRow, ActiveCol), Cells(LastRow, ActiveCol))
        .NumberFormat = "General"
        .FormulaR1C1 = "=RC[-4]&""OFF"""
    End With

 

[Eaglboy1]

And this is why I love these forums. Thank you Netuser! Works like a charm now!