Count the number of occupied cells in a Named Range between two No's. the same and repeat down the range until a empty cell is found

I  am 57 years old and have just started using Excel 2003 (and hopefully soon to have excel 2007) to keep myself occupied.

I have set my self a task and have been creating a worksheet to predict the UK Lottery numbers each week. 

I realise that the odds of winning are massive and it is very unlikely for me to actually win this way, but unless I try – who knows. And since the object of the exercise is to teach myself Excel Programming Including VBA (especially since I am actually enjoying it) – then it doesn’t really matter if creating an Excel worksheet that actually cuts the odds of winning or not. 

I am working on various ways to reduce the odds including Benford's Law but I am stuck, an was wondering if may be someone could at least point me in the right direction. 

What I wish to do is:-

 Given a column of numbers in a named array (or what ever) 52 cells long, there are numbers between 1 & 49 with in the cells of this array and since I have just started inputting the data into this list I have only filled in say 10 of the cells out of 52 in this first column. 

Now since Bedford Law indicates that the first 30.10299957% of numbers picked start with the letter "1", 17.60912591% start with a "2" and so on, what I would like to do is to find the first "1"in the range (whether it be a "1"or the first "1" in "10" or "15" and so on) and then count the cells occupied with a number between this first "1" and the next 1"" found down the list, and then from the last "1" found to the next down the list and so on until all the 1’s have been found in this column and all the number counted between, until there are no more ones or we reach an empty cell. 

I would like to place each count found in separate cells in a named array (or what ever) so that I can analyze in this case, the number of weeks number 1 wasn’t picked. 

The same process will then have to be carried out with the numbers 2, 3 & 4. (2, 20, 21…29), (3, 30, 31…….39) and (4, 40, 41…….49). 

Since this is an array or list for the first number picked out the machine I would then repeat this process for the next 5 numbers picked from the machine for the next 52 weeks. The first numbers picked are in column One, the second number picked are in column two and so on until you have six columns. Six being the number of balls picked (I don’t concern myself with the bonus ball) each week to make up the Lottery draw. 

I believe that VBA is possibly the way to go for this function although to  be quite honest I  haven’t got a clue until I do it so if anyone has a solutions, suggestions or can point me in the right direction I would be very much appreciate it.

What do you know already?

Have you got a design for the code, even in high-level English terms?

Can you construct a loop to look through each cell? Do you know how to extract the first character of a cell's reference?

 Although I am a beginner I have been producing basic Excel worksheets for serveral years using some of the most common functions.

Since I've always wanted to improve my Excels Skills - I though what better than to create a more complicated worksheet now I have time due to illness.

Apart from my recent posting I have only an idea in my head and an actual working worksheet - but it now appears that I have a lot more to learn about the way in which I set about achieving the result I would like.

I have got has far as using the following formula:-

To find the address of the first Number, where K77 in this case is the number I am looking for to start my count and Latest1 is a Named Range of a single column 52 cells long. Which has the latest data including this weeks result for the first ball picked. However at present I only a few cells filled as I am still developing the worksheet and have just started recording the results.

The First Workshhet contains the following:-

I have Named Ranges which include Latest1 ...to Latest6(the last filled cell in Latest1 being this weeks 1st number picked and the cell above that , the 1st number picked last weeks so on).

The next Named Range is called Previous1... to Previous6.

Previous1 data is exactly the same as Latest1 with the exception that the top cell in Latest1 is this weeks result and the top cell in Previous1 is the 1st ball picked last week. Being side by side so that I can tell the difference between this weeks pick and last weeks pick at a glance.

There are six groups of data comprised of two colums each - Latest1 & Previous1 (together), Latest2 & Previous2 (together.).. to Latest6 & Previous6 (together) and so on.. I have kept the 1st, 2nd ... 6th balls picked  ranges together for easy analysis.

Assuming that I am looking for the number "1" I presume if I can now loop the above formula in some way, I would then be able to find the address of the next "1" and then count the cells in between, assuming all contain numbers - store the result of the count in an individual cell for later analysis, save the address of the last "1" and repeat the process until I run out of "1's" or hit an empty cell in this range. Each count being saved in an individual cell - be it an array or Named range.

I will then use these Data Ranges to analyse the various forms of data collected  in such a way as to include the uses of Bedford's Law and others in the hope of reducing the odds in my favour.

In Worksheet 2 you have  graph1 of the various result from Ball pick No.1 (Latest1 & Previous1), in worksheet to a graph2 of the results of ball pick no.2 and so on to worksheet 6. I will also being doing other analysis with the assistance of the Graph's on these worksheets.

There are other column Named Ranges attached to the 6 sets of data but to mentioned these, I feel, would only confuse the issue. That's if I haven't already done so - by my poor explination.

I am hope this helps but if I can help further I will do my best.

Bob - May I also take this opportunity to thank you for your reply.

 I have been working on this during the night and have so far come up with this.

Being a relative beginner it probably isn't the bestsolution but here goes:-

To find the first No in the 52 cell range named Latest2 (K6:K57)I have written this:-

=ADDRESS(ROW(Latest2)+MATCH(K77,Latest2,0)-1,COLUMN(Latest2),4,1) which I placed in C75 just to test it.

To find the second No in the range I used :-

=ADDRESS(ROW(Latest2)+MATCH(K77,Latest2,1)-1,COLUMN(Latest2),4,1) which I placed in C76.

This gave me the correct relative addresses of: K6 & K11.

I then entered the following formula to count the numbers in between the two addreses:-

=COUNT(C75:C76)

However =Count read c75 as C75 and C76 as C76 and returned a value  of 0.

I would very much appreciate it if someone would kindly tell me where I am going wrong.

 This will work as long as the column you are referencing is between A and Z (basically a one-letter column character).

=INT(RIGHT(C57,LEN(C57)-1)-INT(RIGHT(C56,LEN(C56)-1)))

By the way, the COUNT function will not work with text.  To count numbers and text, you'll have to use the COUNTA function.

 Good Luck!

 Hi Mike,

Thank you very much for your reply - which does work in the sernerio I have given.

Where the resulting address from =ADDRESS(ROW(Latest2)+MATCH($W$77,Latest2,0),COLUMN(Latest2),4,1) is in c57 - address = k7

and the result of =ADDRESS(ROW(Latest2)+MATCH($W$77,Latest2,0)-1,COLUMN(Latest2),4,1) is in C56 - address = K6

(Your formula) gives a result of Zero (which is correct)

When I come to get the next Cell address reference using the new found address references the formula looks like this:-

The answer will be place in c58

=ADDRESS(ROW(C57:K57)+MATCH($V$77,C57:K57,0),COLUMN(C57:K57),4,1) - I don't know if this is correct but it seems to be take the cell ref as c57 and not as the address reference in its contents which is K7.

Unfortunately the Named Rages (columns) are also in C,K,S,AA,AI & AQ.

Please consider  the following:-

K6, K7 ..............................................................K15 the cells in a named column range being filled as below respectively.( I have placed it in a row to save space in this reply - having tried it the other way first)

11,17,23,20,19,14,17,5,30,49,25,17,23,45,32,15 and followed by "x" - 57 empty cells as they haven't been filled with results yet (the list is made up for testing purposes - anyway)

What I am trying to do:-

find the address of the first number starting with 1 (which in this case is 11 in K6)

Find the next number starting with 1 which is 17 in K7

Count the numbers between and store the result in the first empty call in a Named range(Column named "Results1") which at the moment could be placed any where on the worksheet.

The process is then repeated finding the next address with the contents, being a number starting with 1 in this case 14 in K11.

Count the cells containg numbers between K7 & K11  and store it in the next empty cell in column range named Results1

Followed by the count between K12 and K17 and so on.

Please Note it is advisable and/or easier to have the 6 Named ranges altogether to achieve my objective either as seperate named ranges (columns) say in column a,b,c,d,e,f - or putting then in a single array  A6:F57 (& named all_results)  - I would be more than happy to experiment with this, since the over all objective is for me to become a lot, lot more familiar and hopeful a lot, lot better at using Excel in as many ways as I can.

I am hoping that perhaps I have explained what I am after this time a little clearer than my last two or three efforts.

In the meantime, whilst waiting and praying an answer may come from either you goodself or from one of the many other experts users out there like you - I'll will continue to find the solution myself using your formula and any others I can think up.

Hopefully in this way I will still be learning even if it might be the hard way.

Kind Regards