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.

C

charlie_jd

New Member
Joined
Jan 14, 2020
Messages
12
Office Version
  1. 365
Platform
  1. 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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Sort by date Sort by votes
Hi & welcome to MrExcel.
Is there a specific column that will always have a value in the last used row?
 
Upvote 0
Fluff said:
Hi & welcome to MrExcel.
Is there a specific column that will always have a value in the last used row?
Click to expand...
Hi, so there is a set number of columns that wil not change but number of rows will vary. Did i answer your question??
 
Upvote 0
No, fraid not.
Is there a certain column that will always have data in the last row, regardless of what that row is?

Also as you are converting the data into a table & your code uses the column labels, can you post some sample data that shows the structure of your data.
You can use the XL2BB add-in to post sample data to the thread. XL2BB
 
Upvote 0
Book1
ABCDEFGHIJKLMNO
1Bet NO.H TeamA TeamBet onPOddsTot. Cap
21ChelsMan UA WIN0.42.5$3,456.00
31ChelsMan UDRAW0.42.7$3,456.00
41ChelsMan UH WIN0.29.8$3,456.00
51ChelsMan U3-00.3458.7$3,456.00
61ChelsMan Uboth s0.673.6$3,456.00
71ChelsMan UH 00.325.7$3,456.00
81ChelsMan UH 10.434.1$3,456.00
Sheet1





Also all columns wil have data in the last row.
 
Upvote 0
Thanks for that, how about
VBA Code: 
Sub charlie_jd()
   Dim UsdRws As Long
   UsdRws = Range("F" & Rows.Count).End(xlUp).row
   
   Range("H1").Value = "IP"
   Range("H2:H" & UsdRws).FormulaR1C1 = "=1/RC[-2]"
   Range("I1").Value = "Edge"
   Range("I2:I" & UsdRws).FormulaR1C1 = "=RC[-4]-RC[-1]"
   With ActiveWorkbook.Worksheets("Sheet1").Sort
      .SortFields.Clear
      .SortFields.Add2 Key:=Range("I1") _
        , SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
        .SetRange Range("A1:I" & UsdRws)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Range("J1").Value = "% Stake"
    With Range("J2:J" & UsdRws)
      .FormulaR1C1 = "=((((RC[-4]-1)*RC[-5])-(1-RC[-5]))/(RC[-4]-1))"
      .Style = "Percent"
      .NumberFormat = "0.00%"
   End With
   ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1:K" & UsdRws), , xlYes).Name = _
        "Table1"
   With ActiveSheet.ListObjects("Table1")
      .TableStyle = "TableStyleLight9"
      .ShowTotals = True
   End With
   Range("Table1[[#Totals],[% Stake]]").FormulaR1C1 = "=SUM([% Stake])"
   Range("K1").Value = "% of cap"
   Range("K2:K" & UsdRws).FormulaR1C1 = "=[@[% Stake]]/Table1[[#Totals],[% Stake]]"
   Range("L1").Value = "Min Bet"
   Range("L2:L" & UsdRws).Value = "2"
   With Range("Table1[Min Bet]")
      .HorizontalAlignment = xlCenter
      .VerticalAlignment = xlBottom
      .WrapText = False
      .Orientation = 0
      .AddIndent = False
      .IndentLevel = 0
      .ShrinkToFit = False
      .ReadingOrder = xlContext
      .MergeCells = False
      .NumberFormat = "$#,##0.00"
   End With
   Range("Table1[[#Totals],[Min Bet]]").FormulaR1C1 = "=SUM([Min Bet])"
   Range("M1").FormulaR1C1 = "After cap"
   Range("M2:M" & UsdRws).FormulaR1C1 = "=[@[Tot. Cap]]-Table1[[#Totals],[Min Bet]]"
   Range("N1").Value = "stake exl min"
   Range("N2").FormulaR1C1 = "=[@[% of cap]]*[@[After cap]]"
   Range("O1").Value = "Stake"
   Range("O2:O" & UsdRws).FormulaR1C1 = "=[@[stake exl min]]+[@[Min Bet]]"
   Range("N2").Select
End Sub
 
Upvote 0
Unfortunately that didnt work, it only worked on the first row. Was i meant to copy it into the original code somewhere?
 
Upvote 0
No, that code is to replace yours & it should work on the entire data.
If I start of with

Book1
ABCDEFGHIJKLMNO
1Bet NO.H TeamA TeamBet onPOddsTot. Cap
21ChelsMan UA WIN0.42.53,456.00
31ChelsMan UDRAW0.42.73,456.00
41ChelsMan UH WIN0.29.83,456.00
51ChelsMan U3-00.3458.73,456.00
61ChelsMan Uboth s0.673.63,456.00
71ChelsMan UH 00.325.73,456.00
81ChelsMan UH 10.434.13,456.00
91ChelsMan UA WIN0.42.53,456.00
101ChelsMan UDRAW0.42.73,456.00
111ChelsMan UH WIN0.29.83,456.00
121ChelsMan U3-00.3458.73,456.00
131ChelsMan Uboth s0.673.63,456.00
141ChelsMan UH 00.325.73,456.00
151ChelsMan UH 10.434.13,456.00
16
List


I end up with

Book1
ABCDEFGHIJKLMNO
1Bet NO.H TeamA TeamBet onPOddsTot. CapIPEdge% Stake% of capMin BetAfter capstake exl minStake
21ChelsMan UA WIN0.42.53,456.000.400.00%2.68051E-17$2.0034289.1888E-142
31ChelsMan UDRAW0.42.73,456.000.370370370.029629634.71%0.017042766$2.00342858.4226025260.42260252
41ChelsMan UH WIN0.29.83,456.000.1020408160.09795918410.91%0.039508231$2.003428135.4342149137.4342149
51ChelsMan U3-00.3458.73,456.000.1149425290.23005747125.99%0.094137766$2.003428322.7042633324.7042633
61ChelsMan Uboth s0.673.63,456.000.2777777780.39222222254.31%0.196680077$2.003428674.2193033676.2193033
71ChelsMan UH 00.325.73,456.000.1754385960.14456140417.53%0.063493369$2.003428217.6552702219.6552702
81ChelsMan UH 10.434.13,456.000.2439024390.18609756124.61%0.08913779$2.003428305.5643457307.5643457
91ChelsMan UA WIN0.42.53,456.000.400.00%2.68051E-17$2.0034289.1888E-142
101ChelsMan UDRAW0.42.73,456.000.370370370.029629634.71%0.017042766$2.00342858.4226025260.42260252
111ChelsMan UH WIN0.29.83,456.000.1020408160.09795918410.91%0.039508231$2.003428135.4342149137.4342149
121ChelsMan U3-00.3458.73,456.000.1149425290.23005747125.99%0.094137766$2.003428322.7042633324.7042633
131ChelsMan Uboth s0.673.63,456.000.2777777780.39222222254.31%0.196680077$2.003428674.2193033676.2193033
141ChelsMan UH 00.325.73,456.000.1754385960.14456140417.53%0.063493369$2.003428217.6552702219.6552702
151ChelsMan UH 10.434.13,456.000.2439024390.18609756124.61%0.08913779$2.003428305.5643457307.5643457
16Total2.7612198041428
17
List
Cell Formulas
RangeFormula
H2:H15H2=1/F2
I2:I15I2=E2-H2
J2:J15J2=((((F2-1)*E2)-(1-E2))/(F2-1))
K2:K15K2=[@[% Stake]]/Table1[[#Totals],[% Stake]]
M2:M15M2=[@[Tot. Cap]]-Table1[[#Totals],[Min Bet]]
N2:N15N2=[@[% of cap]]*[@[After cap]]
O2:O15O2=[@[stake exl min]]+[@[Min Bet]]
J16J16=SUM([% Stake])
K16K16=SUBTOTAL(103,[% of cap])
L16L16=SUM([Min Bet])
 
Upvote 0
Forgot to mention that the code is using col F to find the last row (as that's where the formula i col H is looking), but it sounds as though col F has no data, in which case change the F on this line
Rich (BB code): 
UsdRws = Range("F" & Rows.Count).End(xlUp).row
to A or any column that has data
 
Upvote 0
I changed what you said about column F but this still ocurrs...


Book1
ABCDEFGHIJKLMNO
1Bet NO.H TeamA TeamBet onPOddsTot. CapIPEdge% Stake% of capMin BetAfter capstake exl minStake
21ChelsMan Uboth s0.673.63,456.000.2777777780.39222222254.31%0.196680077$2.003428674.2193033676.2193033
31ChelsMan Uboth s0.673.63,456.000.2777777780.39222222254.31%0.196680077$2.0034282
41ChelsMan U3-00.3458.73,456.000.1149425290.23005747125.99%0.094137766$2.0034282
51ChelsMan U3-00.3458.73,456.000.1149425290.23005747125.99%0.094137766$2.0034282
61ChelsMan UH 10.434.13,456.000.2439024390.18609756124.61%0.08913779$2.0034282
71ChelsMan UH 10.434.13,456.000.2439024390.18609756124.61%0.08913779$2.0034282
81ChelsMan UH 00.325.73,456.000.1754385960.14456140417.53%0.063493369$2.0034282
91ChelsMan UH 00.325.73,456.000.1754385960.14456140417.53%0.063493369$2.0034282
101ChelsMan UH WIN0.29.83,456.000.1020408160.09795918410.91%0.039508231$2.0034282
111ChelsMan UH WIN0.29.83,456.000.1020408160.09795918410.91%0.039508231$2.0034282
121ChelsMan UDRAW0.42.73,456.000.370370370.029629634.71%0.017042766$2.0034282
131ChelsMan UDRAW0.42.73,456.000.370370370.029629634.71%0.017042766$2.0034282
141ChelsMan UA WIN0.42.53,456.000.400.00%2.68051E-17$2.0034282
151ChelsMan UA WIN0.42.53,456.000.400.00%2.68051E-17$2.0034282
16Total2.7612198041428
Sheet1
Cell Formulas
RangeFormula
H2:H15H2=1/F2
I2:I15I2=E2-H2
J2:J15J2=((((F2-1)*E2)-(1-E2))/(F2-1))
K2:K15K2=[@[% Stake]]/Table1[[#Totals],[% Stake]]
N2N2=[@[% of cap]]*[@[After cap]]
M2:M15M2=[@[Tot. Cap]]-Table1[[#Totals],[Min Bet]]
O2:O15O2=[@[stake exl min]]+[@[Min Bet]]
J16J16=SUM([% Stake])
K16K16=SUBTOTAL(103,[% of cap])
L16L16=SUM([Min Bet])



I appreciate all the help.
 
Upvote 0
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
178
Coyotex3
C
C
  • Question
Loop/Repeat Macro
2
Replies
11
Views
397
kvsrinivasamurthy
K
L
  • Solved
VBA remove leading zeros
2
Replies
14
Views
1K
Joe4
Joe4
L
  • Question
Variable Rows, Help Speeding Up
Replies
4
Views
328
kevin9999
K
C
  • Question
How to get my macro to stop at the last recognized line
Replies
6
Views
247
Coco1
C

Forum statistics

Threads
1,215,452
Messages
6,124,914
Members
449,195
Latest member
Stevenciu

Share this page

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
Back
Top