I've recorded a macro but it only works on the rows I recorded on and want it to work on every row that has data.

[charlie_jd]

Hi, I am new to macros and recorded one on a set of values however the amount of rows may vary from time to time and the macro doesn't change with the change of rows. Any help would be great as the code is overwhelming to me. Cheers.

Sub Stake()
'
' Stake Macro
'

'
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "IP"
    Range("H2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=1/RC[-2]"
    Range("H2").Select
    Selection.AutoFill Destination:=Range("H2:H8"), Type:=xlFillDefault
    Range("H2:H8").Select
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "Edge"
    Range("I2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=RC[-4]-RC[-1]"
    Range("I2").Select
    Selection.AutoFill Destination:=Range("I2:I8"), Type:=xlFillDefault
    Range("I2:I8").Select
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add2 Key:=Range("I2:I8") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Sheet1").Sort
        .SetRange Range("A1:I8")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "% Stake"
    Range("J2").Select
    ActiveCell.FormulaR1C1 = "=((((RC[-4]-1)*RC[-5])-(1-RC[-5]))/(RC[-4]-1))"
    Range("J2").Select
    Selection.AutoFill Destination:=Range("J2:J8"), Type:=xlFillDefault
    Range("J2:J8").Select
    Selection.Style = "Percent"
    Selection.NumberFormat = "0.0%"
    Selection.NumberFormat = "0.00%"
    Range("A1:K8").Select
    Application.CutCopyMode = False
    ActiveSheet.ListObjects.Add(xlSrcRange, Range("$A$1:$K$8"), , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").TableStyle = "TableStyleLight9"
    ActiveSheet.ListObjects("Table1").ShowTotals = True
    Range("Table1[[#Totals],[Column1]]").Select
    Range("K9").Cut Destination:=Range("J9")
    Range("Table1[[#Totals],[% Stake]]").Select
    ActiveCell.FormulaR1C1 = "=SUM([% Stake])"
    Range("K2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=[@[% Stake]]/Table1[[#Totals],[% Stake]]"
    Range("K2").Select
    Selection.AutoFill Destination:=Range("Table1[Column1]"), Type:= _
        xlFillDefault
    Range("Table1[Column1]").Select
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "Min Bet"
    Range("L2").Select
    ActiveCell.FormulaR1C1 = "2"
    Range("L2").Select
    Selection.AutoFill Destination:=Range("Table1[Min Bet]"), Type:= _
        xlFillDefault
    Range("Table1[Min Bet]").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.NumberFormat = "$#,##0.00"
    Range("Table1[[#Totals],[Min Bet]]").Select
    ActiveCell.FormulaR1C1 = "=SUM([Min Bet])"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "After stake"
    Range("M2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=[@[Tot. Cap]]-Table1[[#Totals],[Min Bet]]"
    Range("M2").Select
    Selection.AutoFill Destination:=Range("Table1[After stake]"), Type:= _
        xlFillDefault
    Range("Table1[After stake]").Select
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "% of cap"
    Range("Table1[[#Headers],[After stake]]").Select
    ActiveCell.FormulaR1C1 = "After cap"
    Range("N2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=[@Column1]*[@[After cap]]"
    Range("N2").Select
    Selection.AutoFill Destination:=Range("Table1[% of cap]"), Type:= _
        xlFillDefault
    Range("Table1[% of cap]").Select
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "Stake"
    Range("O2").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=[@[% of cap]]+[@[Min Bet]]"
    Range("O2").Select
    Selection.AutoFill Destination:=Range("Table1[Stake]"), Type:=xlFillDefault
    Range("Table1[Stake]").Select
    Range("Table1[[#Headers],[Column1]]").Select
    ActiveCell.FormulaR1C1 = "% of cap"
    Range("Table1[[#Headers],[% of cap2]]").Select
    ActiveCell.FormulaR1C1 = "stake exl min"
    Range("N2").Select
End Sub

 

[charlie_jd]

I just need to drag down column "N" in the code please

 

[Fluff]

You need to make this change

   Range("N2:N" & UsdRws).FormulaR1C1 = "=[@[% of cap]]*[@[After cap]]"