On Sep 20, 4:24*pm, Paul Black wrote:
On Sep 20, 4:03*pm, GS wrote:
Paul Black brought next idea :
On Sep 19, 8:04 pm, GS wrote:
Is there some reason you aren't using arrays to do the comparisons? It
would certainly be much faster than reading the wks for each
CombinationDraw, AND not subject to any worksheet function anomolies!
Also, why do you make the 'Option Base 1' statement to only Dim Matched
with a zero base? IMO, declaring 'Option Base 1' is never a good idea
in most cases!<g
--
Garry
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Thanks for the reply Garry.
In fact the code was put together by someone else as I have limited
VBA knowledge.
I have tried to adapt the code but unfortunately have not been able
to.
Is there a simple solution to this?
Kind regards,
Paul
Paul,
There could be a simple solution if it's absolutely clear what it is
that you want to accomplish. Your explanation suggests you want to take
a row of 7 individual numbers (6+bonus) drawn in range1 and compare
them to each combination of a 6 number set of 'wheeled' numbers in
range2.
Since there's already tonnes of freeware out there to do this, I have
to assume this is a school project as it doesn't make sense to spend
the time to figure out how to duplicate what's already been done.
However, if you persist toward a VB solution then try searching for
code samples that find matches using arrays, by looping 1 array (inner
loop) against another array (outer loop).
Example: (air code)
* vNumsDrawn = Range("DrawnNums"): vNumsWheeled = Range("WheeledNums")
* For n = LBound(vNumsDrawn) To UBound(vNumsDrawn)
* * For i = LBound(vNumsWheeled) To UBound(vNumsWheeled)
* * * If vNumsDrawn(n, 1) = Empty Then
* * * * Exit For '//check another value
* * * Else
* * * * If vNumsWheeled(i, 1) = vNumsDrawn(n, 1) Then
* * * * * iMatches = iMatches + 1
* * * * * 'other processing...
--
Garry
Free usenet access athttp://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc
Hi Garry,
Unfortunately I left school in 1979, many moons ago.
The code does work if there are no gaps in the data in Range1.
I have a spreadsheet with all the data in and just wanted to run the
code from a button within the designated sheet.
Thanks for your help and advice, I will google your suggestions.
Kind regards,
Paul
Hi Garry,
I am using Excel 2007.
I searched code samples that find matches using arrays, by looping 1
array (inner loop) against another array (outer loop).
Unfortunately I could not adapt my findings into a working code,
probably because of by limited understanding of VBA.
Anyway, I have tried to put in extra criteria using SUMPRODUCT instaed
of COUNTIF to only count if the cell or combination is greater than
blank but without any success.
The SUMPRODUCT still returns a count on the blank data.
Kind regards,
Paul