help for vba problem

Reply #17

Elek — Sun, 13 Sep 2020 16:58:58 GMT

Wow

Well, it's nothing, it works beautifully. Thank you for your efforts.

I test and learn in the meantime, because I have something to learn, two to three times faster than what I have DIY put together so far.

But progress can't stop, so I'm thinking a lot more,

And I will add to my list the things I wrote earlier:

I will then try to calculate the probability that all numbers can be included in the next draw. (I would add that this is also vague, but see if something comes... [ More ]

Reply #16

Elek — Sun, 13 Sep 2020 13:30:10 GMT

Hi phileight

Thank you very much

I'll review your solution soon.

Of course, I will notify you of my comment immediately.

I add as I slept one of my thoughts, the solution came from repetition.

It's not nice but I think it works, based on the former.

If ag = Sheets3.Cells(hislr, 4).Value Then 'The first element of the last line of historical data

rep1 = 1

Else

rep1 = 0

End If

...

repeat = rep1 + rep2 + rep3 + rep4 + rep5 + rep6 + rep7

If repeat 0 O... [ More ]

Reply #15

phileight — Sat, 12 Sep 2020 21:09:06 GMT

Elek

see link below. do not include this link in any reply as it would cause you to violate a forum policy. i think this includes every thing you have asked for. you will need to replace the test data with your history data. questions let me know

https://www.dropbox.com/s/xezgvqyhztrlblm/elek.xlsm?dl=0

Reply #14

Elek — Sat, 12 Sep 2020 10:43:11 GMT

Have a nice day phileight

If you re already kind enough to deal with it, please take that into account.

While trying the generator and filtering, I also saw an opportunity to repeat the numbers, such as:

Week 4 - 3, 10, 13, 30, 31, 33, 34

Week 5 - 8, 10, 22, 26, 28, 32, 34

2 numbers are repeated, their location is not the same, of course, only in this case, and then I filter the possibilities.

I also want to filter this somehow:

I don't know the code here

If r... [ More ]

Reply #13

phileight — Fri, 11 Sep 2020 16:13:45 GMT

had not heard from you for a while. I have alredy forgotten what i did. let me review this thread to get it all in mind again. as a first guess, rather than provide a bit of code i will try to provide a file you can download. give me a couple days

Reply #12

Elek — Fri, 11 Sep 2020 08:05:33 GMT

Hi phileight

I tried to solve things with more or less success.

I have answered the second point above, I am icing the third one for the time being.

For the first, my solution is:

ah = Sheet2.Cells (i, 4) .Value

... 'so I'll record the historical data

hd (ah) = 1: hd (bh) = 1: hd (ch) = 1: hd (dh) = 1: hd (eh) = 1: hd (fh) = 1: hd (gh) = 1

then I load it into the array and the code you provide will continue to work the same as well as scan only a specific area... [ More ]

Reply #11

Elek — Fri, 14 Aug 2020 08:28:19 GMT

Also, I still have to add to the previous ones, I realized what was wrong with this:

a = Cells (hl, dl): hd (a) = 1

All it takes is that there are serial numbers in column 'A',

Column 'B' dates and is also included in the calculation.

Reply #10

Elek — Fri, 14 Aug 2020 07:34:32 GMT

Dear phileight

Ok, I tried my second problem and answered it that way.

Not a solution I came up with because then the sum filter is not closed and cannot run. In all cases, you must click Go to rejected.

Then only Problems 1 and 3 remained.

Thanks

Hi Elek

Reply #9

Elek — Fri, 14 Aug 2020 07:27:11 GMT

Dear phileight

I had some free time, looked at his modifications and it works beautifully. Thank you for your efforts.

However, my stupidity is basic design. So I would have three more questions.

1. I would transfer the historical data to Sheet1, which would be called Drws , and also draw to the D3: J-last line. Where should I change the path and reference of hd (35)? The generated number strings would be in the range B3: H103 as Sheet2 'Gen'.

2. If I put another filter o... [ More ]

Reply #8

phileight — Tue, 04 Aug 2020 23:46:14 GMT

adding the comment will prevent checking your history.

for checking sum see code in green below

Sub allgen()

Dim gd(35) ' array for generated data

Dim hd(35) ' array for historical data

Dim iRow As Long

Dim FirstRow As Long

Dim LastRow As Long

Dim sor_szam As Integer

Dim gorgo As Variant

'Munka1.Range( L3:R100 ).ClearContents

timebegin = Now()

oldStatusBar = Application.DisplayStatusBar

Application.DisplayStatusBar = True

Application.StatusBar... [ More ]

Reply #7

phileight — Tue, 04 Aug 2020 23:20:34 GMT

based on your code i set the dimension of this array to 35. if the highest number in your data is greater than 35 that would cause this problem. check the value of a when it fails set your dimension value to match or a bit higher.

Reply #6

Elek — Tue, 04 Aug 2020 18:17:35 GMT

Welcome phileight

I tried your code, I haven't measured vba so much yet.

Writes in the area marked in yellow (Runtime error 9).

...

For dl = 1 To 7 ' number of data elements

a = Cells(hl, dl): hd(a) = 1

Next dl

...

If I comment on the given code part, the program will run. but I think it should be added

e.g.

a = Cells(hl, dl)': hd(a) = 1

I also noticed another narrowing option. The sum of the numbers.

How could it be inserted into the code?

In my case... [ More ]

Reply #5

Todd — Tue, 04 Aug 2020 11:34:30 GMT

Reply #4

Elek — Tue, 04 Aug 2020 02:32:36 GMT

Dear phileight

Thank you very much.

I'll review your code throughout the day.

Drawing 103 was included as an example only and I tried my code.

As soon as I succeed, I signal

Thanks

Goodbye Elek

Reply #3

Elek — Tue, 04 Aug 2020 02:27:56 GMT

Dear phileight

It is about 2200 drawings

I would like to exclude all 7, in principle there was no repetition in our lottery yet

Now I'm studying excel vba, the repair is on a smaller level,

but i hope if i get stuck you can help me.

Reply #2

phileight — Tue, 04 Aug 2020 01:01:06 GMT

see below. i cannot run this code without the data. i have put a blank line before and after the changes i made and made them red. your code indicates there is only 103 draws in your history so that is the number i used. i have tried to put in enough comments so you can understand what the program is doing. enjoy

Sub allgen()

Dim gd(35) ' array for generated data

Dim hd(35) ' array for historical data

Dim iRow As Long

Dim FirstRow As Long

Dim LastRow As Long

Dim sor_sza... [ More ]

Reply #1

phileight — Mon, 03 Aug 2020 23:33:29 GMT

questions. how many previous drawings do you want to check?

how close a match should be rejected? all seven digits, 6, only 5.

do you know how to edit the code if a patch is provided

help for vba problem

Elek — Mon, 03 Aug 2020 18:33:25 GMT

Hi everybody

I was mistaken in the topics

I'm sorry, but I want help with the excel vba problem below

I turn to you because I have a question.

The following document lists the extracted numbers and a generated list.

I ran into drawing lots of numbers that had already been played.

How could the number strings that have already been drawn be filtered out in the generator?

So some cleverness should be added to the code below!

Thanks for the help.

I don't know how to put... [ More ]