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?

Please Login or Register  to view this content.

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.

Please Login or Register  to view this content.

Or

Please Login or Register  to view this content.

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.