Posts by VBAwant2b

Important Notice


Please note that on 14th December 2023 users will experience an expected outage whilst we make upgrades to our network. We anticipate this process may take a couple of hours and so we apologise in advance for any inconvenience.

    Hi Carim,


    I moved the formula you created for me yesterday to another workbook. When I tried to reopen the workbook this morning, I get a message to update links.


    Why can't I use that formula in another workbook without having to link it to the workbook that it was created in?


    I made a worksheet reference change in the formula from Data! to DB! which is an exact copy of the Data worksheet in Test SBLO V3.xlsm


    Test SBLO V3 - {=COUNTA(Data!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}


    New Workbook - {=COUNTA(DB!$A:$A)-MAX(IF(ListN1=A6,ROW(ListN1)-MIN(ROW(ListN1))+1))-1}


    Does it have anything to do with "ListN1"? I really don't understand that part of the formula. What is ListN1? I'll research ROW function more.


    I'd really need to be able to use the formula without having to link it to another workbook.


    When I try to break the links, it warns me to save a copy. I'm not sure what is going on. I'll try to research this more myself because I need to understand why I can't use the formula as written.


    I wonder if a subroutine is the way to go.

    Almost. Very close.


    Your formula result (C25), "7" for #24 is correct. My manual count, "6", was incorrect.


    I tried to enter a few numbers for the next drawing to see how the formulas on Sheet SBLO update and they don't exactly.


    Enter 39, 40, 41, 42, 43 as the next winning numbers on the Data Sheet and see what results you get for SBLO.


    Also, try entering the last winning numbers 2, 6, 11, 13, 31 as the winning numbers for the next drawing to see what you get. The formula update should be "0" for each number. I'm getting "-1" as a result.


    Lastly, whenever I enter the winning numbers for the next drawing, all of the results should update with an additional skip.


    In other words, if #7 didn't hit on the next drawing and the formula results show it had "1" skip, when I enter the next winning numbers, although "7" wasn't one of the winning numbers, the formula should update to show it's now "2" skips IF it were to hit in the next drawing.

    Let me review Test SBLO V2.

    Just looking at it, I don't need Columns C-G.


    I think Column A may be correct and what I need.


    The need for a subroutine was to get results without filling a sheet with formulas in each cell but I can always Copy/Past Value. This is just one part of many different calculations I'll be doing.


    I appreciate your efforts. Let me review.

    Sheet1(2) is just an example of code that counts the number of skips between hits for winning numbers. I only included it to show the subrountine used to find that.


    What I was hoping to find was a modification of that subroutine that counted the number of skips for ALL 43 numbers with the results displayed in Sheet2 Column B.

    1. B7 (5), 5 drawings since the #1 (A7) was hit/picked/selected. See Sheet1 C872.


    If you entered 1, in C878 of Sheet1 and counted the number of drawings between the last time 1 hit (Sheet1 C872), it would be 5 drawings.


    2. B8 (0), the number 2 (A8) was picked/hit/selected in the previous drawing on (Sheet1 C877), so, there would be no skips/drawings between it IF #2 was picked/hit/selected for the next drawing.


    3. B9 (12), 12 is the number of drawings between the next drawing that would be entered in one of the cells (C878:G878) IF 3 were picked/hit/selected in the next drawing.


    Five numbers out of 43 will be entered as the next drawing in Sheet1 C878:G878. I don't know which five out of the 43 it will be but I'd like to know how many skips it would be between drawings for each of the 43 numbers that could be drawn with the count placed in Column B.


    If I were to enter 1, 2, 3, 4, 5 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 5, 0 , 12, 47, 9 in H878:L878.


    The result of 5 in H878 is number of drawings since the #1 occurred in a previous drawing. The result of 0 would be the number of skips for the #2 in the previous drawing. The #3 had 12 skips since it last occurred in a previous drawing. Etc. for the #4, 47 skips since it last occurred in a previous drawing. Nine skips for the #5 since it last occurred in a previous drawing.


    If I entered 6, 7, 8, 9, 10 in Sheet1(2) C878:G878 and click on the "Test SBLO" command button it would give me the result of 0, 1, 4, 3, 12 in H878:L878.


    Instead of entering 1, 2, 3, 4, 5 and clicking the command button, get the results, then entering 6, 7, 8, 9, 10, get the results, etc. etc. through 39, 40, 41, 42, 43, I'd like to modify the Sub attached to the "Test SBLO" command button to count the number of skips between the last occurrence for all 43 numbers of which 5 will be picked/hit/selected. Place the count of skips in Column B of Sheet2 for each number in Column A.

    Let me add this.


    Imagine on the next drawing, instead of selecting 5 numbers out of 43 numbers, they drew all 43 numbers. Just for giggles.


    I want to know how many skips between the last occurrence for each number (all 43 numbers).


    I'd like to see that result in Column B on Sheet2.


    Hope that helps make it more clear.

    Sheet1


    IDDateN1N2N3N4N5


    8472/27/20211016172436
    8482/28/202169131422
    8493/1/202126182333
    8503/2/2021915202734
    8513/3/20211922232435
    8523/4/2021613203336
    8533/5/2021314152036
    8543/6/2021112152043
    8553/7/20211115161843
    8563/8/20211630313940
    8573/9/2021320253043
    8583/10/20211820252829
    8593/11/2021210132930
    8603/12/2021518193839
    8613/13/2021511202126
    8623/14/2021712252841
    8633/15/20211337383943
    8643/16/2021310112241
    8653/17/20211516171937
    8663/18/20211824283139
    8673/19/2021513222734
    8683/20/20211516212437
    8693/21/20211314182429
    8703/22/2021918323334
    8713/23/2021112183236
    8723/24/202168132936
    8733/25/2021912162035
    8743/26/2021628333639
    8753/27/202167222332
    8763/28/202126111331


    Sheet2

    #SBLO
    15
    20
    312
    447
    59
    60
    71
    84
    93
    1012
    110
    123
    130
    147
    158
    163
    1711
    185
    1911
    203
    218
    221
    231
    246
    2514
    2615
    279
    282
    294
    3017
    310
    321
    332
    346
    353
    362
    378
    3813
    392
    4020
    4112
    4237
    4313


    Sheet1 contains a list of drawings. The numbers are in Columns C through G. One row for each drawing.

    Sheet2 contains numbers 1-43 of which 5 will be selected for the next drawing.


    In Sheet2 Column B (SBLO) is where I want to place the number of drawings Skipped Between the Last Occurance for each number in Column A (#).


    The Sheet2 example has the results I'm seeking in Column B (SBLO) according to the latest drawings in Sheet1.

    [IMPORTANT] I didn't template the full history of drawings in my Sheet1 example. The #4 on Sheet2 shows 47 skips between last occurance but to save space I didn't list the drawings that far back.

    Ex. 2-6-11-13-31 was the last drawing on Sheet1 so they will have 0 skips between last occurance on Sheet2. The drawing before that on Sheet1 was 6-7-22-23-32. Numbers# 7, 22, 23, 32 on Sheet2 will have 1 skip between last occurance, 6 already has 0, etc. etc.

    Lame attempt below. This is stuff I mashed together from my little subroutine library but it definitely has big holes in it. I'm far from being VBA proficient. I only know enough to be dangerous and always frustrated.

    Thanks in advance to anyone willing to help.