Need help with EXCEL Formulas/Functions please

Discussion regarding the spreadsheet functionality of Bet Angel.
Post Reply
User avatar
Bot2020
Posts: 28
Joined: Wed Feb 12, 2020 1:48 pm

Hi All,

Can anyone kindly, dearly, show me the formula I have in mind for a stratedgy...

T.P= target profit C.B = current balance B.S = Back Stake E.S.T = Event start time!

target profit - current balance = BACK STAKE and to PLACE/TRIGGER this bet ONLY! @ 10 seconds before Event Start Time!

T.P - C.B = B.S @ E.S.T
i.e. 20.00 - 15.47 = 4.53 @ 01:29:50 am ...if the event were to start at 01.30am (I believe the current balance is always to be cell 'C6')


...I would be so greatful! I look forward to your response!
Top
Anbell
Posts: 2054
Joined: Fri Apr 05, 2019 2:31 am

If you are using excel main, and not VBA, then you have to put your target in a separate cell, say AA1.

Then in the stake column you enter =AA1-C6
In the odds column you put the stake
In the Bet Rules column you say If (C3= x, "BACK","")

edited to add: you should probably put your trigger time in a different cell, say AA2, then your Bet Rules formula would be =If(C3=AA2,"BACK","")
Top
User avatar
Bot2020
Posts: 28
Joined: Wed Feb 12, 2020 1:48 pm

Hi Anbell,
I just wanna say thankyou, for your help I've managed to get the formula in and created a new cell for trigger timer (T.T.) IN CELL F5 and target profit (T.P.) IN CELL F6... came up with this formula ...

=IF(AND(F5<F4, G9<2.3),"BACK","LAY" and so far it has worked out for me thankyou for applying a theory to get my head round...i couldnt establish the A column so i was unable to apply your exact method but managed to get my method from your theory thankyou

Image https://ibb.co/5RCvmty
(I've highlighted in red to illustrate what im saying) ;)


I do have a queries though Anbell...

Q1. Is it possible to 'Signal favourite' on Excel-'Bet rules'? if so how to do it?

Q2. How do I go about linking more then 10 markets in 'Excel sheet' (Column), as the dropdown in Guardian only allows you to link upto 10 Sheets and after 'Bet angel (10)' has 'blank' as an option.

Is their a way to link up more then 10 markets? to the BetAngel_Multiple.xls file.... and if so how can I do it? thanks


Abel.
Top
Anbell
Posts: 2054
Joined: Fri Apr 05, 2019 2:31 am

Bot2020 wrote: ↑
Sun Mar 29, 2020 9:25 pm
 Hi Anbell,
I just wanna say thankyou, for your help I've managed to get the formula in and created a new cell for trigger timer (T.T.) IN CELL F5 and target profit (T.P.) IN CELL F6... came up with this formula ...

=IF(AND(F5<F4, G9<2.3),"BACK","LAY" and so far it has worked out for me thankyou for applying a theory to get my head round...i couldnt establish the A column so i was unable to apply your exact method but managed to get my method from your theory thankyou

Image https://ibb.co/5RCvmty
(I've highlighted in red to illustrate what im saying) ;)


I do have a queries though Anbell...

Q1. Is it possible to 'Signal favourite' on Excel-'Bet rules'? if so how to do it?

Q2. How do I go about linking more then 10 markets in 'Excel sheet' (Column), as the dropdown in Guardian only allows you to link upto 10 Sheets and after 'Bet angel (10)' has 'blank' as an option.

Is their a way to link up more then 10 markets? to the BetAngel_Multiple.xls file.... and if so how can I do it? thanks


Abel.
Sorry - I answered this the other day but it didnt post for some reason.

I see that Dallas has answered q2 elsewhere.

re q1, one way to do it is to put all your prices and horse numbers in a list somewhere and use the excel MIN function to find the mininum price. You'd then use the VLOOKUP or XLOOKUP to pick up the horse number
Top
User avatar
Bot2020
Posts: 28
Joined: Wed Feb 12, 2020 1:48 pm

Anbell wrote: ↑
Thu Apr 02, 2020 6:46 am
Bot2020 wrote: ↑
Sun Mar 29, 2020 9:25 pm
 Hi Anbell,
I just wanna say thankyou, for your help I've managed to get the formula in and created a new cell for trigger timer (T.T.) IN CELL F5 and target profit (T.P.) IN CELL F6... came up with this formula ...

=IF(AND(F5<F4, G9<2.3),"BACK","LAY" and so far it has worked out for me thankyou for applying a theory to get my head round...i couldnt establish the A column so i was unable to apply your exact method but managed to get my method from your theory thankyou

Image https://ibb.co/5RCvmty
(I've highlighted in red to illustrate what im saying) ;)


I do have a queries though Anbell...

Q1. Is it possible to 'Signal favourite' on Excel-'Bet rules'? if so how to do it?

Q2. How do I go about linking more then 10 markets in 'Excel sheet' (Column), as the dropdown in Guardian only allows you to link upto 10 Sheets and after 'Bet angel (10)' has 'blank' as an option.

Is their a way to link up more then 10 markets? to the BetAngel_Multiple.xls file.... and if so how can I do it? thanks


Abel.
Sorry - I answered this the other day but it didnt post for some reason.

I see that Dallas has answered q2 elsewhere.

re q1, one way to do it is to put all your prices and horse numbers in a list somewhere and use the excel MIN function to find the mininum price. You'd then use the VLOOKUP or XLOOKUP to pick up the horse number
Hi Anbell,

thanks for replying eventualy, is it not possible to automate this!? or do i have to keep applying MIN Function and Vlookup manual for every sheet
Top
Anbell
Posts: 2054
Joined: Fri Apr 05, 2019 2:31 am

Bot2020 wrote: ↑
Sat Apr 04, 2020 8:47 pm
thanks for replying eventualy, is it not possible to automate this!? or do i have to keep applying MIN Function and Vlookup manual for every sheet
You can have a section in your sheet that creates a list by pointing to the prices. eg.
cell A700 "=g9"
cell A701 "=g11"
etc


then do a MIN at the bottom of that list in cell eg A750

then do a VLOOKUP in cell A752, for example

THen your formula that needs to know what the fave is can refer to cell A750
Top
User avatar
Bot2020
Posts: 28
Joined: Wed Feb 12, 2020 1:48 pm

Im having slight mis understan with this formula,

Maybe because I dont have a Column A, and for some reason bet angel template doesnt have it.

I added a link/attachedment ... https://ibb.co/Dw2tvDy

could you possibly apply the formula to this data attached to give me a better understanding please.

Abel.
Top
Anbell
Posts: 2054
Joined: Fri Apr 05, 2019 2:31 am

You can use any column
Top
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

So I decided to delve into the world of Macro's to help me sort out the way I want to see my Data, basically it is very time consuming to go through each Market I.D count the number of runners, copy 5 of the colums & paste them individually onto a seperate Sheet. I basically have Hcap2,3,4,5,6,7 so on and so on... I copy the BSP, High, Low, PPVolume & VWAP and then I use this to get the avg per for each field size.

I recorded a macro using absolute & relative references but it only seems to work a few times before it misses out a row from each of the columns... my best guess is that because the cell number changes everytime i change the Market I.d, but in all honesty, i'm clueless & this is a learning curve for me. Can this be done? I'll post the code from the macro but like I said, it a whole new world to me so dont laugh too hard.

Sub HcapThirteen()
'
' HcapThirteen Macro
'

'
Range("K8573").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("Hcap13").Select
ActiveWindow.SmallScroll Down:=-54
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A2").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste
Sheets("Filter_Data").Select
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 7
Range("N8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hcap13").Select
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
Range("A17").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste
Sheets("Filter_Data").Select
Range("O8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hcap13").Select
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=15
Range("A32").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste
Sheets("Filter_Data").Select
Range("S8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hcap13").Select
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 39
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 14
ActiveWindow.ScrollColumn = 13
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 10
ActiveWindow.ScrollColumn = 9
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=21
Range("A47").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste
Sheets("Filter_Data").Select
Range("L8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Hcap13").Select
ActiveWindow.ScrollColumn = 64
ActiveWindow.ScrollColumn = 63
ActiveWindow.ScrollColumn = 62
ActiveWindow.ScrollColumn = 61
ActiveWindow.ScrollColumn = 60
ActiveWindow.ScrollColumn = 59
ActiveWindow.ScrollColumn = 58
ActiveWindow.ScrollColumn = 57
ActiveWindow.ScrollColumn = 56
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 12
ActiveWindow.ScrollColumn = 11
ActiveWindow.ScrollColumn = 8
ActiveWindow.ScrollColumn = 7
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 4
ActiveWindow.ScrollColumn = 3
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveWindow.SmallScroll Down:=15
Range("A62").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste
Sheets("Filter_Data").Select
ActiveWindow.ScrollColumn = 6
ActiveWindow.ScrollColumn = 5
ActiveWindow.ScrollColumn = 2
ActiveWindow.ScrollColumn = 1
ActiveCell.Offset(0, -11).Range("SourceData__2[[#Headers],[EVENT_ID]]").Select
End Sub
Top
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Ahhh, I see you’ve pressed the ‘Record Macro’ button to do this. Firstly, you don’t need any of the window scrolling lines. The whole point of macros is they run in the background quickly so visually, who cares what is happening. I don’t see any problem with you using this method to start with. At this stage, have things work, keep things neat and see if there are parts you can condense down. Less code = more speed. There’s a fine line between flimsy and efficient though.

I haven’t made it loads better but there’s a bit more structure now (you can see each chunk is every time the system swaps to a new sheet). Next thing you need to do is see if you can make it even less clunky. Do you really need to swap between two sheets 10 times or so? This takes time to process. I’m guessing the answer is no.

Fair play for diving into this area! I suggest you watch some basic YT vids on macros, namely using variables and loops. Should get you started. It’s a long process so the way I learned was to create clunky code like this and then kept watching vids and picking things up to make my system better and better.

Code: Select all

Sub HcapThirteen()

‘’’Sheets(“???”).Select		‘’’Fill this line in manually, which sheet does it start on?

Range("K8573").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Hcap13").Select

Range("A2").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("N8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A17").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("O8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A32").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("S8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A47").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("L8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A62").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

ActiveCell.Offset(0, -11).Range("SourceData__2[[#Headers],[EVENT_ID]]").Select

End Sub
Top
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

CallumPerry wrote: ↑
Fri Apr 10, 2020 4:47 pm
 Ahhh, I see you’ve pressed the ‘Record Macro’ button to do this. Firstly, you don’t need any of the window scrolling lines. The whole point of macros is they run in the background quickly so visually, who cares what is happening. I don’t see any problem with you using this method to start with. At this stage, have things work, keep things neat and see if there are parts you can condense down. Less code = more speed. There’s a fine line between flimsy and efficient though.

I haven’t made it loads better but there’s a bit more structure now (you can see each chunk is every time the system swaps to a new sheet). Next thing you need to do is see if you can make it even less clunky. Do you really need to swap between two sheets 10 times or so? This takes time to process. I’m guessing the answer is no.

Fair play for diving into this area! I suggest you watch some basic YT vids on macros, namely using variables and loops. Should get you started. It’s a long process so the way I learned was to create clunky code like this and then kept watching vids and picking things up to make my system better and better.

Code: Select all

Sub HcapThirteen()

‘’’Sheets(“???”).Select		‘’’Fill this line in manually, which sheet does it start on?

Range("K8573").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Hcap13").Select

Range("A2").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("N8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A17").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("O8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A32").Select
Selection.End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("S8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A47").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

Range("L8573").Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Hcap13").Select

Range("A62").End(xlToRight).Select
ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select
ActiveSheet.Paste

Sheets("Filter_Data").Select

ActiveCell.Offset(0, -11).Range("SourceData__2[[#Headers],[EVENT_ID]]").Select

End Sub
Thanks Calumn, once I have it working for one it should be a breeze for the others & yes, it makes sense now that I dont need to keep swapping between the sheets each time.
Top
CallumPerry
Posts: 575
Joined: Wed Apr 19, 2017 5:12 pm
Location: Wolverhampton

Oops, that didn't come out much neater, let me try again...

Just to be clear too, if it wasn't working properly before, it may not work now but it may help you identify the problem. If it is quicker/more efficient then the code may complete where otherwise it was being cut short due to BA refreshing the cells again so it could just work, I'll be honest, I haven't looked at the code at all. I've taken out a few things which were jumping out at me to help you get to the next stage.

Code: Select all

Sub HcapThirteen()


‘’’Sheets(“???”).Select		‘’’Fill this line in manually, which sheet does it start on?


Range("K8573").Select

Range(Selection, Selection.End(xlDown)).Select


Selection.Copy



Sheets("Hcap13").Select


Range("A2").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select



Range("N8573").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.Copy


Sheets("Hcap13").Select



Range("A17").End(xlToRight).Select



ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select



ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("O8573").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.Copy


Sheets("Hcap13").Select


Range("A32").End(xlToRight).Select


ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select


ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("S8573").Select


Range(Selection, Selection.End(xlDown)).Select


Application.CutCopyMode = False


Selection.Copy


Sheets("Hcap13").Select



Range("A47").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("L8573").Select


Range(Selection, Selection.End(xlDown)).Select



Application.CutCopyMode = False



Selection.Copy


Sheets("Hcap13").Select



Range("A62").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select



ActiveCell.Offset(0, -11).Range("SourceData__2[[#Headers],[EVENT_ID]]").Select


End Sub
Top
jamesg46
Posts: 3769
Joined: Sat Jul 30, 2016 1:05 pm

CallumPerry wrote: ↑
Fri Apr 10, 2020 4:55 pm
 Oops, that didn't come out much neater, let me try again...

Just to be clear too, if it wasn't working properly before, it may not work now but it may help you identify the problem. If it is quicker/more efficient then the code may complete where otherwise it was being cut short due to BA refreshing the cells again so it could just work, I'll be honest, I haven't looked at the code at all. I've taken out a few things which were jumping out at me to help you get to the next stage.

Code: Select all

Sub HcapThirteen()


‘’’Sheets(“???”).Select		‘’’Fill this line in manually, which sheet does it start on?


Range("K8573").Select

Range(Selection, Selection.End(xlDown)).Select


Selection.Copy



Sheets("Hcap13").Select


Range("A2").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select



Range("N8573").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.Copy


Sheets("Hcap13").Select



Range("A17").End(xlToRight).Select



ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select



ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("O8573").Select

Range(Selection, Selection.End(xlDown)).Select

Application.CutCopyMode = False

Selection.Copy


Sheets("Hcap13").Select


Range("A32").End(xlToRight).Select


ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select


ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("S8573").Select


Range(Selection, Selection.End(xlDown)).Select


Application.CutCopyMode = False


Selection.Copy


Sheets("Hcap13").Select



Range("A47").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select


Range("L8573").Select


Range(Selection, Selection.End(xlDown)).Select



Application.CutCopyMode = False



Selection.Copy


Sheets("Hcap13").Select



Range("A62").End(xlToRight).Select

ActiveCell.Offset(0, 1).Range("Table49[[#Headers],[BSP]]").Select

ActiveSheet.Paste


Sheets("Filter_Data").Select



ActiveCell.Offset(0, -11).Range("SourceData__2[[#Headers],[EVENT_ID]]").Select


End Sub
I've just realised the reason I was going back and forth from each sheet was because the data is coming out of one table into 5 separate tables. Thanks for taking the time to chop out the unnecessary bits, it may take me some time to figure it out but I guess it's worth while long term.
Top
Jukebox
Posts: 1576
Joined: Thu Sep 06, 2012 8:07 pm

I wish some of the Health Ministers and responsible politicians would post a few questions on this thread - The standard of data analysis and their understanding of the the graphs they present and look for "green shoots" on has been awful.
Top
User avatar
ShaunWhite
Posts: 9731
Joined: Sat Sep 03, 2016 3:42 am

jamesg46 wrote: ↑
Fri Apr 10, 2020 5:02 pm
 it may take me some time to figure it out but I guess it's worth while long term.
If you're not using vba/macros/functions etc then you're only skimming the surface of Excel.. Even if you only spend an hour a day looking at it, by the end of the month you'll already be well on the way because even though it's a big scary topic you only actually need about 10% of everything in the User Guides. You'll go from doing what you can with front end features to doing anything you can conceive of.

Tbf you only need to learn very little, read a cell from a given place into a variable, do some sums, write a variable back to a cell in a given place. Then understand loops and the slight difference in the syntax of IF, and that's about it really. Like Lego it's not what fancy bricks you've got but how you put the simple ones together.
Top
Post Reply

Return to “Bet Angel - Spreadsheet / Excel chat”