Kenneth Hobson
Active Member
I think it is going up from the last row and then down one. So, add a blank value above where you want the first output to go. The blank value is not really blank. Add one or more spacebar characters. Sneaky?
Need help with VBA Code for generating lotto numbers
- Thread starter dwrowe001
- Start date
- Status
Yeah, sneaky... that worked but when I delete outputs to start fresh I will probably always delete that blank/space in that cell... is there way to put in the code that way I won't have worry about always having to put a space in the cell above where I want the output to go?
Kenneth Hobson
Active Member
If you can think it, you can likely do it.
Since the code goes something like: set r = cells(rows.count, "G").end(xlup)).offset(1), we can check the row number. If r.row < 7 then set r = Range("G7")
Since the code goes something like: set r = cells(rows.count, "G").end(xlup)).offset(1), we can check the row number. If r.row < 7 then set r = Range("G7")
LOL... not with everything... I once thought I could fly... that didn't go so well....
where should I put this string??
and do I put all this in?:
set r = cells(rows.count, "G").end(xlup)).offset(1), we can check the row number. If r.row < 7 then set r = Range("G7")
Kenneth Hobson
Active Member
Code:
If rS.Row < 7 Then Set rS = ws.Cells(7, rS.Column)
Generate:Ok, I found where it should go... I put it here:
as as you can see I changed the 7's to 5's since it was outputting the numbers a little to far down.
I would like to move the numbers over one column so that they are all under their appropriate position, starting in F, G, H, I, and J.. I looked for where that would be in the code but couldn't find it.. I thought it was in the last part of the string you had me put in, the 5, rS.column, but it wasn't that.. at least it didn't do anything when I changed the 5... where abouts is that part?
Attachments
Kenneth Hobson
Active Member
Things like that are usually near the start or top.
As you can see, column E is the start. The 5 does not mean much unless you want row 5 rather than 7. Change the code in #31 post if needed. e.g.
IF you didn't "get" it, Generate: label was just shown so you could find where to add the line above it. Ctrl+F makes it easy to find such things...
Code:
'Title Cell for first random number set on Picks sheet
Set rStart = ws.Range("E5")As you can see, column E is the start. The 5 does not mean much unless you want row 5 rather than 7. Change the code in #31 post if needed. e.g.
Code:
If rS.Row < rStart.row Then Set rS = ws.Cells(rStart.row, rS.Column)
Generate:IF you didn't "get" it, Generate: label was just shown so you could find where to add the line above it. Ctrl+F makes it easy to find such things...
If rS.Row < rStart.row Then Set rS = ws.Cells(rStart.row, rS.Column)
Generate:
does this code do the same as Changing the E5??
Kenneth Hobson
Active Member
No. rStart is the main one. rS is the one that checks rStart and does what you want. In normal practice, one might want to change rStart. The new rS should never need changing.
Think of it this way. Most will set row 1 values as column headers/titles. The code as it was, would have put the first value in row 2. Howsoever, had the user deleted all data in that column, the first value would be in row 2 still but that just looks silly with no column title...
Think of it this way. Most will set row 1 values as column headers/titles. The code as it was, would have put the first value in row 2. Howsoever, had the user deleted all data in that column, the first value would be in row 2 still but that just looks silly with no column title...
Ugh, this makes my head hurt.. Ken, I really can't thank you enough for all you have done for me.. A thousand times thank you!!! As I stated in one of my previous posts, I visited several other VB forums requesting help with this, no body responded with much, if anything at all. You stated in one of your posts tonight "If you can think it, you can likely do it." I knew what I was asking for help with could be accomplished with VB, I just knew it.... I also knew it was beyond my capabilities.. I can grasp the very rudimentary functions of VB, but when it starts getting complicated (like what you came up with) I'm lost. I just had to find the right site and person.. I finally did.
Thanks again!!
Dave
Kenneth Hobson
Active Member
IF still confused, see extra comments for rStart and rS that I added.
Code:
Sub bGenerate()
Dim ws As Worksheet, rStart As Range, rS As Range
Dim i As Long, ii As Integer, j As Long, k As Long
Dim tf As Boolean, a(1 To 5), b
Dim r As Range, rr
Set ws = Picks
Set r = Picks.Range("A1", Picks.Range("A1").End(xlDown))
'Remove possible duplicate values
rr = UniqueArrayByDict(r.Value) 'Base 0 array.
'Title Cell for first random number set on Picks sheet
'******** Change to suit. Output will start here or next empty row below.**********
Set rStart = ws.Range("E5")
'******** Change to suit. Output will start here or next empty row below.**********
'Check if any preferred numbers were set.
'All blank or all numbers means no preference.
For i = 1 To 5
a(i) = Val(Picks.OLEObjects("TextBox" & i).Object.Value)
If a(i) = 0 Then
j = j + 1
Else
k = k + 1
End If
Next i
'Ok to generate all 5 numbers?
If j = 5 Or k = 5 Then
tf = True
Else 'Add numbers that may not be in rr.
For k = 0 To 4
If a(k + 1) <> 0 Then
ReDim Preserve rr(0 To UBound(rr) + 1)
rr(UBound(rr)) = a(k + 1)
End If
Next k
End If
'Remove possible duplicate values that were added.
rr = UniqueArrayByDict(rr) 'Base 0 array.
If Not IsNumeric(Picks.tSet.Value) Then Picks.tSet.Value = 1
For i = 1 To Picks.tSet.Value
'Next blank cell in Picks sheet for next set of numbers
Set rS = ws.Cells(Rows.Count, rStart.Column).End(xlUp).Offset(1)
'******** Sets rS based on rStart for location of output. *********************************
If rS.Row < rStart.Row Then Set rS = ws.Cells(rStart.Row, rS.Column)
Generate:
b = RndIntPick(1, UBound(rr) + 1, 5) 'Base 0 array.
'Set elements in b to rr values.
For k = 0 To 4
b(k) = rr(b(k) - 1) 'Random picks from col A in b now.
Next k
'Resort b
b = ArrayListSort(b)
If Not tf Then
For ii = 1 To 5
If a(ii) <> 0 And b(ii - 1) <> a(ii) Then GoTo Generate
Next ii
End If
'Fill textbox values and rStart cell or next blank row below it.
For ii = 0 To 4
Picks.OLEObjects("TextBox" & ii + 1).Object.Value = Format(b(ii), "0#")
rS.Offset(, ii).Value = b(ii)
Next ii
Next i
End SubDang it Ken... I did it again..... I messed it up. I added a button to the Pick sheet named Update List so that I can pull in the list of numbers from my other worksheet of my workbook and put the list in column A of Pick sheet.. it works when I leave all textboxes blank to generate all numbers. it doesn't work when I enter a digit into one or more of the textboxes ... getting this error:
Attachments
Ken,
This is the code I'm using with the button I added the Pick Sheet.. still having the problem mentioned above in post #39.. can you help?
Code:
Sub RowsOfNumbersToSingleColumn()
Dim Cell As Range, Joined() As String, Nums(1 To 60) As String
For Each Cell In Sheets("Predictions").Range("D12:R12,D14:R14,D16:R16,D18:R18,D20:R20,D32:M32,D33:K33,D34:I34,D36:M38,D37:K37,D38:I38")
If CLng(Cell.Value) > 0 And CLng(Cell.Value) <= 60 And Len(Cell.Value) > 0 Then Nums(CLng(Cell.Value)) = Format$(Cell.Value, "00")
Next
Joined = Split(Application.Trim(Join(Nums)))
With Sheets("Picks").Range("A1").Resize(UBound(Joined) + 1)
.NumberFormat = "@"
.Value = Application.Transpose(Joined)
End With
End SubKen,
I'm lost. I've tried to figure it out, but I have no clue what the problem is.
I don't know what I did to cause it to not function properly.
NARAYANK991
Excel Ninja
Hi ,
If you can upload your complete workbook with all of the code in it , you might get help from other members.
If you can only post the code , you can wait till Kenneth responds.
Narayan
If you can upload your complete workbook with all of the code in it , you might get help from other members.
If you can only post the code , you can wait till Kenneth responds.
Narayan
Hi,
I am uploading my workbook now... issue is on the Picks worksheet.
there are basically two ways to generate numbers:
1. there are 5 pick boxes, textboxes R1,2,3,4,5. if all boxes are blank or empty, when you click the generate button the code will generate sets of 5 numbers picked from the numbers in column A, based on the number in the sets box.
issue I'm having is when I want to select specific numbers instead of having the code pick them.. for example, if I put 05 in R1 and 25 in R3, and select 4 sets, the code should generate 4 groups of 5 numbers:
05 10 25 30 43
05 12 25 35 53
05 17 25 38 54
05 20 25 43 56
the two numbers I put in are put in for each group generated, and the code selects the remaining 3 numbers. Or I could put numbers in 4 of the 5 boxes, and have the code select the remaining 1 number. this is when the error is appears. Also, the numbers I put in manually don't have to be in the column A list.
It was working... Ken came up with the code, I did something to it some how..
Hope you can figure out where the problem is.. I think it's when I copied the code for the Update List button... but I'm not 100% sure of this.
If I could ask, the Clear All button clears the Textboxes when clicked... Could you create another button which would clear all generated numbers.. leave the Clear All button.. just add another one for Clearing all generated numbers?
The error I get is in my previous post #39
Thank you for helping.
Dave
Last edited:
NARAYANK991
Excel Ninja
Hi ,
There was no problem with the code ; the problem was with the data item 56 , which had been entered as text instead of as a number.
Correct this and see if the code works correctly.
Once you confirm this , we will look at your second request.
Narayan
There was no problem with the code ; the problem was with the data item 56 , which had been entered as text instead of as a number.
Correct this and see if the code works correctly.
Once you confirm this , we will look at your second request.
Narayan
Hi Narayan,
I see what you're saying, I stored numbers in the column as numbers, then tried generating some numbers, worked.. thing is though, I would like leading zeros for single digits, and it was working before that way... don't know why it suddenly changed??
Dave
Narayan,
After thinking about it, maybe what I remember working was the uploaded workbooks from Ken, which are pure numbers in column A.. and then once I copied the macros ect.. over my original copy of the workbook, that's when I started having problems... so, question is, can it be fixed to use numbers as text or some other way to get leading zeros?
All,
here is a better copy of my workbook.. it works better.
on the predictions page, I have a couple things working to choose numbers.. then, on the picks page, the macro on the update list button, pulls those numbers in.. I would like leading zeros used throughout the sheets.
Hope this helps.... thank you all for helping.
Dave
here is a better copy of my workbook.. it works better.
on the predictions page, I have a couple things working to choose numbers.. then, on the picks page, the macro on the update list button, pulls those numbers in.. I would like leading zeros used throughout the sheets.
Hope this helps.... thank you all for helping.
Dave
Kenneth Hobson
Active Member
Does using custom format 0# for those cells where needed not suffice?
Of course once you set it as Text format, you should first select those cells and all that might ever be numbers, click the warning arrow and Convert to Number. Then set the selection of cells to the custom format.
Of course once you set it as Text format, you should first select those cells and all that might ever be numbers, click the warning arrow and Convert to Number. Then set the selection of cells to the custom format.
Ken,
yes, I did what you said above and that works... so then I try updating the column A list numbers, and when i do the numbers in column A go back to text..
Kenneth Hobson
Active Member
Are you talking about Column A on Picks Sheet? I did the 2 fixes and it worked fine. The code does not change the format type for my runs.
As I said, a user could enter '1 so that would be text. You can control that error by setting a Data Validation for Whole Numbers. That even lets you set a range like 1 to 100.
As I said, a user could enter '1 so that would be text. You can control that error by setting a Data Validation for Whole Numbers. That even lets you set a range like 1 to 100.
- Status