charlie_jd
New Member
- Joined
- Jan 14, 2020
- Messages
- 12
- Office Version
- 365
- Platform
- Windows
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.
VBA Code:
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