Macro help applying formula to dynamic number of rows for different files (s/b EASY)

Hello,

I have a question that should have a simple answer:

I'm new to creating macros and trying to create a macro that will perform a formula that I can use for multiple files. However the files are a varying number of rows.

I want the formula to carry out on the entire column (all rows). However when I record the macro (I tried using relative and also tried using absolute references when filling the formula) the formula always seems to stop filling wherever my original file's rows stopped. I wrote the formula for the first row and double clicked it to fill the remaining rows.

For example if the macro I recorded in File 1 goes to row 15, when I run the macro on File 2 which is 20 rows, the formula stops filling at row 15.

What am I doing wrong? Please help! Sounds like something simple I'm overlooking. Thanks.

Can you post the code?

Sub StatusView_QuotaCheck()
'
' StatusView_QuotaCheck Macro
' BWW Status Viewer- Subtracting Total - Excluded in order to paste into Quota Check
'

'
Rows("1:2").Select
Selection.Delete Shift:=xlUp
Columns("B:B").ColumnWidth = 14.43
Range("R1").Select
ActiveCell.FormulaR1C1 = "Actual"
Range("R2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]-RC[-9]"
Range("R2").Select
Selection.AutoFill Destination:=Range("R2:R997")
Range("R2:R997").Select
Columns("R:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("B:Q").Select
Range("Q1").Activate
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("A1").Select
ActiveCell.FormulaR1C1 = "Store ID"
Range("A1").Select
With Selection.Font
.ColorIndex = xlAutomatic
.TintAndShade = 0
End With
Range("A1:B1").Select
Range("B1").Activate
Selection.Font.Bold = True
Cells.Select
With Selection.Font
.Name = "Verdana"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
With Selection.Font
.Name = "Times New Roman"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.TintAndShade = 0
.ThemeFont = xlThemeFontNone
End With
Selection.Font.Size = 9
Selection.Font.Size = 10
Cells.Select
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
With Selection.Interior
.Pattern = xlNone
.TintAndShade = 0
.PatternTintAndShade = 0
End With
Range("B3").Select
End Sub

You need to use code tags when posting. Does this do what you want?

Sub yankeekid86()
'
' StatusView_QuotaCheck Macro
' BWW Status Viewer- Subtracting Total - Excluded in order to paste into Quota Check
'
Rows("1:2").Delete Shift:=xlUp
Columns("B:B").ColumnWidth = 14.43
Range("R1") = "Actual"
With Range("R2:R997")
    .FormulaR1C1 = "=RC[-1]-RC[-9]"
    .Value = .Value
End With
Columns("B:Q").Delete Shift:=xlToLeft
Cells.Interior.ColorIndex = xlNone
Range("A1") = "Store ID"
Range("A1").Font.ColorIndex = xlAutomatic
Range("A1:B1").Font.Bold = True
Cells.Font.Name = "Times New Roman"
Cells.Font.Size = 10
Range("B3").Select
End Sub

No that didn't work, it didn't run all the way down. To rephrase my question:
I need to copy the formula down to the last row. (You know, like when you double click in the corner of a cell and it copies the formula to the last row with data in the adjoining column).

When I double click while recording my macro it just references the actual column and row number, but it won't always be the same as the spreadsheet grows daily. So I need a "copy to last row" formula for the macro. But the last row will change everytime I run it.

Is there anyway to fix this while recording rather than in the code?

I think the problem is here:
Selection.AutoFill Destination:=Range("R2:R997")

I want the Range to be changeable- to wherever the last row is.

OK.

Sub yankeekid86()
'
' StatusView_QuotaCheck Macro
' BWW Status Viewer- Subtracting Total - Excluded in order to paste into Quota Check
'
Rows("1:2").Delete Shift:=xlUp
Columns("B:B").ColumnWidth = 14.43
Range("R1") = "Actual"
With Range("R2:R" & Activesheet.usedrange.rows.count)
    .FormulaR1C1 = "=RC[-1]-RC[-9]"
    .Value = .Value
End With
Columns("B:Q").Delete Shift:=xlToLeft
Cells.Interior.ColorIndex = xlNone
Range("A1") = "Store ID"
Range("A1").Font.ColorIndex = xlAutomatic
Range("A1:B1").Font.Bold = True
Cells.Font.Name = "Times New Roman"
Cells.Font.Size = 10
Range("B3").Select
End Sub

Or

Selection.AutoFill Destination:=Range("R2:R" & activesheet.usedrange.rows.count)

Thanks that works! When I record the macro (not in VBA code) is there a way to get this to work correctly? Thanks again!

You can't record a dynamic range. You'd have to modify after you record.