Posts by jonny1984

    Hi all


    I am a (relative) newbie in Excel .. have some basic VBA skills .. but be gentle !!


    I am trying to scrape odds data from a range of bookmakers into Microsoft Excel and have the odds updating if/when the website changes.


    Essentially I am trying to create a sheet that looks a bit like this ...... (although I don't need all the bookmakers !! Just 1 or 2 would be a great start


    https://easyodds.com/horse-rac…n/355027/win-and-each-way


    Ideally it should be able to pull a list of the days racing from the front page , here


    https://easyodds.com/horse-racing



    Create a new Excel sheet for each race and populate with the data .


    What is really important (and annoying!!!) is I want to try to match the data to the individual bookmaker site (because there is a delay on easyodds.com) - so for example , in the wolverhampton event with the web link above - for the first horse listed , "Axe Axelrod" - it would need to
    - find the 1.20 Wolverhampton race on bet365 website (https://www.bet365.com/?&cb=103254828#/AS/B2/) .......
    - then find that link to the race (https://www.bet365.com/?&cb=10…/E20598146/F75756415/P10/)
    - find the horse ("Axe Axelrod") and then return the odds attibuted to that horse


    I am sure this is actually not as complicated as it might sound for someone with the correct HTTP / GET / JSON skills .. if anyone is able to just setup a very basic example code that I could then follow, copy and expand on - I would be very very grateful !!


    Thanks!! :)


    Jonny

    Hi


    I have a macro that I am running which takes a very long time to run - I even built a Timer function to calc and on average it takes about 8 minutes to complete!!


    I have tried a lot of the usual "tricks" - e.g. Calculation = Manual, SceenUpdating = False, EnableEvents = False


    But these don't seem to speed it up much


    LOT OF DATA : It may just be a case there is a lot of data and this is how long it will take to process. The sheet has circa 350,000-400,000 rows. And about 25-30 columns. Pretty much all with formulas in .. so perhaps its just simply the amount of data I am trying to process.


    With all the formulas I wrote some VBA to enter the code into all the cells . But the formula only technically needs to go in once in the top row and then just be copied down as the formula remains the "same" (adjusting automatically for the row number)



    For example .... My Code -:


    Code
    1. ' Enter formula in column J and copy down to last row (lreur)
    2. Sheets("Data").Range("J4:J" & lreur).FormulaR1C1 = _
    3. "=IF(RC[-2]=""Long"",RC[-1]*(1-Master!R5C3),IF(RC[-2]=""Short"",RC[-1]*(1+Master!R5C3)))"



    Basically it could enter that formula into just Cell J4 .. and then copy it down through all the rows.. would that be quicker ?


    Is there anything else obvious with the above formula that might slow it down ?


    Waiting 8 mins to run the macro every time makes my work very slow and tedious !!!



    Thanks in advance for any help !


    Jonny


    Mod Edit: I have added code tags for you. Please use them in the future as you agreed to in the forum rules.

    Re: Copying VBA Code For Use in a New Sheet


    I can't get this to work at all - I don't know why


    All I want is some code that does the following:


    (1) Constantly review the Cell $I$3 in all worksheets of a workbook
    (2) If Cell $I$3 = 1 in any of the workbooks, then execute the following :
    (3) "Cut" Range B3:J3 in the sheet which triggered the call (i.e. the sheet in which $I$3=1) .... and paste this as values into the last row in a table (B:J where the row number is the last row in a table below... table occupying cells B10:J99999)
    (4) Then Copy and Paste as Values Cells B1:D1 into Cells B3:D3
    (5) Then Copy and Paste (as Formulas) Cells E1:J1 into Cells E3:J3


    Then end and loop back to checking the cell $I$3 in every sheet of the workbook.


    Every workbook will be laid out the same :
    'Master' Formulas in Cells B1:J1
    'Live' Formulas in Cells B3:J3 (i.e. if cell triggered then this data gets cut to stop the macro looping)
    Data Table B10:J99999 to record the entire row B3:J3 (as values) every time a "1" occurred live in I3


    Does that make sense?


    I have tried copying and pasting your formula, and playing around with it etc but it keeps bugging and erroring, I don't know why.


    Any help is really appreciated - if I manage to get a working code I would very much like to donate to the Royal Marines or a charity of your choice, just let me know, as recognition for your time.


    Thanks again!!
    Jonny

    Re: Copying VBA Code For Use in a New Sheet


    The problem with that code is that it will run if any cell changes in the sheet won't it? But there are several other cells that could change and I don't want it to start copying and pasting on every cell change.


    Ideally it should only run the "macro" (copying and pasting the range) when cell $I$3 = 1


    ..... and that should run for Cell $I$3 on any sheet.... e.g. if cell $I$3=1 on "Group1" then it shd copy and paste on the 'active sheet' (i.e. "Group1").... equally if cell $I$3=1 on "Group2" then it shd copy and paste on the 'active sheet' (i.e. "Group2")


    does that make sense?


    the formula I originally posted works on a single sheet basis.... but bugs if I copy the sheet and rename "group2"... should i just copy my code and reference the sheet in front of the cell reference for each ....? e.g. "Group1.$I$3" and "Group2.$I$3" ??

    Re: Copying VBA Code For Use in a New Sheet


    Hi


    Thanks again for coming back to me - think I remembered why I was using "Calculate" event rather than "SheetChange" event.....


    The cell "$I$3" is a cell contains a formula, the cell is not changed manually. Would your code work if the value of the cell changes by formula?


    Actually I've just tried replacing my existing code with yours and even if I change cell I3 manually it doesn't seem to do anything??


    Thanks!
    Jonny

    Re: Copying VBA Code For Use in a New Sheet


    Hi


    Unfortunately I'm a bit of a novice - a lot of the code is built through copying and pasting bits of code from other bits of this forum to do what I want to do and then stringing it together on something of a trial and error basis!! So I will do my best to answer your questions....


    There is 1 sheet at the moment which is called "Group1"... ultimately I was hoping to be able to copy this sheet to have "Group2", "Group3", "Group4" etc . - and for my code to automatically know that if cell "I3" on any of the sheets changed, then it should execute macro (copying and pasting data) within that sheet only.


    Is there a way to reference your range so that if, for example, Range "I3" changes in sheet "Group3" - then all subsequent activity (copying & pasting etc) would be in the "Group3" sheet?


    I have no idea if the code is meant to be in the worksheet calculate event or would be better somwhere else?? Where would/should it go?


    Any advice very greatly appreciated!


    Thank you so much

    Hi


    I desperately need help. I have some code that I am currently using to populate cells in an existing worksheet.


    However - I now want to copy this sheet - with an identical layout but slightly different parameters defined in the cells.


    The problem is - when I copy the sheet - the VBA code continually errors as it is obviously getting confused which cells in which sheets it should be monitoring, copying, pasting etc


    Could somebody help please!!! Thank you so much in advance!!


    My code:


    Re: Run / Trigger A Macro When a Cell in Sheet Changes....


    Quote from rvasquez;629219

    When is the dynamic data updated? Do you want the data from row 1 pasted into the last row before the sum row if I1 = 1 when the workbook opens? When it calculates? When the user changes a value?


    Thanks!



    The dynamic data will feed from a website where users "login" to register arrival.... so the cells G1 and H1 will be zero the whole time.. and then flick to 1. When it flicks to the value "1" (in either G1 or H1) - I would like the macro to copy the data from row 1 and paste it into the next empty row.


    When the workbook is opened - the macro should just follow same rules (so only paste the row if there is a value "1" in either G1 or H1).


    Ideally the macro will just be constantly checking the value of cells G1 and H1. If either are 1, then it should copy and paste the row. Then the number will very quickly be a 0 again. (I am happy to have duplicated rows if it happens - I can go through these and delete them my Excel skills are ok - I just dont have the VBA skills to program the "dynamic" bit of the macro so it constantly checks G1 and H1 and as soon as the value changes it then executes


    Thanks for your help!!

    Re: Run / Trigger A Macro When a Cell in Sheet Changes....


    Hi!


    Thanks so much for getting back to me... so basically the cells in Columns G and H will be "pasted values".


    What I don't think I made clear is that there will be dynamic data feeding into cells B1:I1 ... this will - in the future - be the next row of data in the table basically.


    Not sure I have laid the sheet out as clearly as I could.... an alternative would be to say "IF CELL I1 = "1"... THEN COPY AND PASTE (as values) ROW 1 INTO THE "X"TH ROW... " with the "xth" row dependent obviously on how many previous entries there are (thats why I tried to base it on the "running total" in cell I20


    So in direct answer to your question - the data in the table (B4:I7) would be all pasted values (previous entries)


    Does that make sense?

    Hi


    I am still what I would describe as a "beginner" in Excel - but have something I urgently want fixed so if anyone can help it would be very much appreciated. I have uploaded the sheet so it will hopefully be clearer what I am trying to achieve - it is in attachments saves as "Acct Management" somewhere below hopefully! Basically this table will be linked into some external dynamic feed that will log people coming in dynamically. The data will feed into the top row (although this is blank at moment) ....so will feed into B1:I1


    In columns in G and H you will see the "0" and "1"s - these can only be a zero or 1 - and the max value can only be 1.


    Lets say "Rob" (in row 7 of my sheet) goes from 0+0=0 to 0+1=1.... this is automatically reflected in the SUM of the totals going up by 1 unit (Cell I20)


    --> What I would like is a macro where if the Total in Cell I20 goes up by 1... then the entire row for Rob (Cells B7:I7) are copied and pasted as values (so they can no longer dynamically update) - and any new data in Cells B1:I1 is then copied and placed into a new row.... which would now be Cells B8:I8


    Is this possible? The macro would have to be able to detect changes to Cell I20 - and then recognise the "Total" number (in cell I20) so that it could use that to calculate the offset of which to row to copy and paste values and where to copy and paste the new row


    Hope that makes sense... If someone could re-work my sheet and re-upload I would be immensely grateful as my programming skills just aren't good enough but this would save me a huge amount of administrative work throughout my day!



    Thank you v much in advance!
    Jonnyozgrid.com/forum/core/index.php?attachment/48552/ozgrid.com/forum/core/index.php?attachment/48552/