Posts by rinconpaul

    Re: MATCH a cell to a Range of a periodically updated list of records

    Quote from pangolin;739828

    put this formula in 1st row and copy down

    =IF(MATCH(A1,INDIRECT(ADDRESS(((ROUNDUP(ROW()/20,0)-1)*20)+1,1,,,"Sheet2")& ":"&ADDRESS(ROUNDUP(ROW()/20,0)*20,1)),0),"LAY","")

    Hi pangolin, and thankyou very much for your response. I can't get the function to update if I paste another 20 rows of data after the last 20 in sheet2. I've made up a simple worksheet for you to look at. I'm sure it's a minor issue.

    Just imagine that a vba formula pastes a fresh set of 20 values to sheet2 each time a new race appears on the sheet1, and then 30 secs later the continually updating values on sheet1 freeze, a comparison is made with the last 20 values in the sheet2 column and the instruction "Lay" is fired if there's a match.


    In a worksheet 'Sheet1', with current race data displayed, a column of values 20 rows long, has a formula in each cell:


    Next column cell:

    =IF(MATCH(A2,Sheet2!A$1:Sheet2!A$20,0),"LAY".....and so on down to row 20

    However the table of values on Sheet2, updates after each race and another table of 20 rows is added. I need the formula in each cell of the next race to be:"


    Next column cell:

    "=IF(MATCH(A2,Sheet2!A$21:Sheet2!A$40,0),"LAY"...and so on down to row 40

    In other words, the active range of cells on Sheet1, is always matching with the next updated range of cells on Sheet2. I would prefer a non vba solution as the active worksheet has a lot of other vba happening that I've finally got stable and don't want to disturb it...LOL

    Re: Combine a Sub Worksheet_Change with Sub Worksheet_Calculate

    Yes you're right Batman, but that code was a remnant from a past failed attempt and was defunct. I should remove it. Whether it's there or not, I don't believe it has a bearing on the case at hand. But you are being thorough to spot it, thanks......But thinking about past failed attempts, do you think that bit of code if corrected and time set at 00:00:00 could replace some of Roy's original code?

    Re: Combine a Sub Worksheet_Change with Sub Worksheet_Calculate

    Hi Roy, thanks for your reply. Bet Angel is software connected to a Betting Exchange (Betfair), so unless you are a subscriber , it won't work for you. The information from Betfair is relayed to a excel work sheet modified to suit (Bet Angel worksheet). I have an attached worksheet (Price Order) which, in the main, duplicates what the Bet Angel sheet displays, but has user defined functions to take that information and trigger a bet back to the BA sheet. The sheet I wish to copy though (the Change Sub) is the Price Order sheet and paste to Sheet 1. Copy_Race is the macro. I just can't understand why, if I put the Change code into the Price Order sheet, why it won't trigger? There are manual trigger buttons there to facilitate, but I need this to run Auto. I'm told the Change Sub has to reside in the Bet Angel sheet to work but there's a conflict with the Calculate Sub. Beyond me! Anyway, no one on the Bet Angel forum can resolve it for me, hence this post. I've attached the workbook anyway. The VB doesn't show the Change Sub that I had in my first post, only because it doesn't work with the Calculate Sub present. The Change sub is fired by a timer reaching 0 and cell Y5 on the Price Order sheet displays "YES". You can manually paste in "YES" to duplicate a Live connection. Good luck, but if too difficult, I understand :) Sorry....but how do you attach a file? Can't see any way?

    I have two Subs that I need to incorporate. I can run one or the other, but not together? Any assistance gratefully acknowledged.

    Re: Using a countdown timer to launch macros

    Thanks John_w, I didn't end up trying your solution. I doubt if it would've worked on the 'Price Order' sheet. I found a solution but it had to be placed on the 'Bet Angel' sheet as that's where the feed comes in to. Unless you had a copy of the commercial software you probably wouldn't have sussed it?

    I have commercial software connected to a betting exchange. The prices, volume data etc are loaded directly to an excel spreadsheet. There is a timer such that when one race finishes the next race, in time order, will load automatically. On another worksheet "Bet Angel", there is a countdown timer in cell F4. It counts in minutes and seconds down to official race start time '00:00:00' and then goes onto a negative countdown until the actual race starts.

    I have macros fired manually by trigger buttons on sheet "Price Order" called Copy_Race and Trigger_Bet. What I would like to do is trigger both macros automatically at a specified time, "let's say 00:00:00". I've tried the following, for one of the macros, without success (you experts will probably laugh?):

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("Bet Angel!$F$4").Value = TimeValue("00:00:00") Then
    Call Copy_Race
    End If
    End Sub

    Finally, because the Global settings will display a new race worksheet within a few minutes, I need the macro to rearm, as it were.

    Thanks for taking the time to read :)