Autofill to the Bottom of the existing Data

Eaglboy1

New Member
Joined
Apr 19, 2016
Messages
20
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:

Code:
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!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

netuser

Active Member
Joined
Jun 19, 2015
Messages
420
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:

Code:
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

New Member
Joined
Apr 19, 2016
Messages
20
That is correct. The entire top section of my macro works great. The only portion I'm having issues with is this portion

Code:
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

Active Member
Joined
Jun 19, 2015
Messages
420
That is correct. The entire top section of my macro works great. The only portion I'm having issues with is this portion

Code:
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,


Code:
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

New Member
Joined
Apr 19, 2016
Messages
20
And this is why I love these forums. Thank you Netuser! Works like a charm now!
 

Forum statistics

Threads
1,144,612
Messages
5,725,315
Members
422,610
Latest member
sanantonio

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top