What if Scenario User from

In the new userform do you want to be able to change the already entered numbers from the Record tab? For example let's assume that you had picked 2, 4, 5 and 22 in columns D to G in the row for 14-Dec-22 10 AM.  Then when the userform was opened it would look like this:

At that point would you like to be able to change one of those 4 numbers (this would not affect the Record tab) or should number entry in the userform be limited to the blank cells? Note that as the picture shows, 2, 4 and 5 are winning numbers but 22 is not.

 At that point would you like to be able to change one of those 4 numbers

Yes please .  

Also I am assuming that I can type my own numbers in the blank cells?

But what if I did not play a draw  and therefore is no data in the Record tab for that day.

How would the What if function or behave ?  

Also I am assuming that I can type my own numbers in the blank cells?

That's correct.

But what if I did not play a draw  and therefore is no data in the Record tab for that day.

I need to think about that.

onI need to think about that.

Would that be the reason initially  you were suggesting the  WNR tab where data is available all the time?

But also remembering currently , the record tab is a record of actually played numbers.

Could I also play or input my own numbers as an op[tion

Would that be the reason initially  you were suggesting the  WNR tab where data is available all the time?

I think that I suggested the Winning Number Frequency (WNF) tab a couple of times and not the WNR, but I realized as I was working on this question that I was thinking of the Prize Table tab which is a hidden tab and not the WNF.

If that is a yes, then the Userform could be placed in any tab, isn't it. ?

Since each of your tabs is different each would need its own code and in a future question we can talk about that.

Since each of your tabs is different each would need its own code and in a future question we can talk about that.

Ok sure

 

In the Record tab:

1. Select the row you are interested in by clicking the row number (like row 68)
2. Click the new 'What If' button
3. Click the userform's 'Calculate Win' button

That will result in the userform's values being colored if they match the WNR row's values, and the prize earned showing up in the box above the 'Calculate Win' button.

At any point you can change the values in the userform.

The 'Clear' button is available to clear all the values if for example if you want to experiment with an unplayed draw.

29251837.xlsb

In below picture, I selected Row 93  and there were 3 hits  49  56  67 for today's 10am draw

but clicking on calculate button , win calculated as 0 , instead of  $ 2

In the workbook that I have there are no WNR records for that row.

But I have updated the workbook till 19 Dec22 at 10am

Perhaps you attached the wrong workbook or you updated your workbook after you attached it because here is my WNR.


In any case if you attach your workbook I can take a look at the problem.

I meant to highlight row 4, not 5.

I rechecked it is correct workbook name : 29251837

I did a server update till 18th Dec and then updated manually for 19th Morning today at 10am

File name for update till 18/12/22 till 6pm

9af99b82-7f1b-11ed-9753-005056819165.xlsx

Updated workbook attached  29251837v19Dec with WhatIf.xlsb

Without changing your workbook I get the proper result.

Steps I followed was  

1) Select row 93 

2) click What if

3_) And hit calculate win

However the result is still $0

Am I missing any step here ?

The attached includes a warning message if What If is clicked and there are isn't a WNR record for the selected row.
29251837a.xlsb

Those steps are correct so try it again.

yes I did, but still shows 0, also there was no warning message on clicking What If

...also there was no warning message on clicking What If

That's expected sice there is a WNR record for that row.

Let me make some changes that might fix your problem.

Try this.
29251837b.xlsb

As soon as I hit calculate win , the above error pops up

Which line is highlighted when that happens?

row 93  , 10 am

I mean in the code, which line is highlighted. (click "Debug").

the yellow line

I updated the draw to 3pm and retested your 2921837a workbook and it suddenly worked.

I am not sure what stopped it from working before

But I tried 4 times and all 4 times it worked.

Selection of Draw period and Time Filter?

How do I select the draw period and time filter to calculate the win for draw numbers 49 54 56 62 67 71 ?

I copied and pasted 19 times into the 10 am draw from 1Dec to 19 Dec and tried to select the draws that way and I got a pop up instead after clicking what if

I copied the above numbers till 19Dec 10am.

Better?
29251837b.xlsb

How do I select the draw period and time filter to calculate the win for draw numbers 49 54 56 62 67 71 ?

As the message says, you need to select the whole row and not just the date.

29251837b.xlsb  ,  B  workbook comes with an error in below picture

We might have to use  your previous A workbook which did not have an error and works fine.

As the message says, you need to select the whole row and not just the date.

Yes I did select Row  3 to Row 93 and yet the message popped saying to select the row.

Do I  select Row 3 , then Row 8, then Row 13 , etc  19 times.  because those rows are for 10 am draw only .

Would it not  be easier and fastest to have an option to select a Draw period in a userform with time filter ? and integrate the draw period userform with time filter into the What if userform ?

One row at a time please.

One row at a time please.

Yes , I did.  but I had to select it 19 times

Could I please have  Draw period option with time filter ? and integrate the draw period userform with time filter into the What if userform ?

I assume you mean that you selected 19 different row, one at a time. If that's true did the correct win amount show up for them?

you only need to select one cell and not the whole row 

one cell at a time is working,  

I have posted a new question