Search for number in string, copy it and strings above, below to different sheet.

[dwrowe001]

To begin with, I am tracking a lottery. This lottery has 6 balls, 5 for the main balls, and a 6th for the bonus ball. The mail numbers for the 5 balls are 1 thru 60. The bonus ball has numbers 1 to 4. I was needing a way to search on a number and then find that number in a list copy that entire row and then move on to the next instance of that same number I was searching. Plus copy and paste the string above it and then two number strings… so I began searching………

I ran across this thread which “My Answer Is This” responded to back on Apr 29, 2019:

Need help with Macro to copy a range

I am not good at drafting Macros from scratch, but I can figure them out after studying and tweaking them for a while. So I studied the macro he created and then modified it to work for my needs. I managed to get it to do what I need… to a degree. Here is what I came up with:

this macro is assigned to button #1:

Sub Sort_1()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowr As Long
Dim Lastrowm As Long
Dim Lastrowp As Long
Sheets("Main").Activate
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row + 2
Lastrowr = Sheets("#1").Cells(Rows.Count, "A").End(xlUp).Row + 1
Lastrowm = Sheets("#1").Cells(Rows.Count, "M").End(xlUp).Row + 1
Lastrowp = Sheets("#1").Cells(Rows.Count, "Y").End(xlUp).Row + 1
Lastrowpp = Sheets("#1").Cells(Rows.Count, "AK").End(xlUp).Row + 1

For i = 1 To Lastrow
If Cells(i, "B").Value = "1" Then
Cells(i, 1).Resize(, 7).Copy Sheets("#1").Cells(Lastrowr, "A")
Lastrowr = Lastrowr + 1
Cells(i - 1, 1).Resize(, 7).Copy Sheets("#1").Cells(Lastrowm, "M")
Lastrowm = Lastrowm + 1
Cells(i + 1, 1).Resize(, 7).Copy Sheets("#1").Cells(Lastrowp, "Y")
Lastrowp = Lastrowp + 1
Cells(i + 2, 1).Resize(, 7).Copy Sheets("#1").Cells(Lastrowpp, "AK")
Lastrowpp = Lastrowpp + 1

End If

Next
Application.ScreenUpdating = True
End Sub

It has issues which I will describe below.

For my spreadsheet, I’m going to have a sheet for each number, making that 60 sheets.
I have included sheets #1 to #6 in the example I’ve uploaded.

Current Issues with the Macro:

• Clicking a Macro run button more then once causes the sort process to run again, resulting in duplication of numbers for that button .. list sorted twice.
• Need a way to prevent re-sorting and duplication of numbers already sorted.
• Best way to add additional numbers to the Macro so that all 60 numbers are in the macro.
• Right now, I have 3 buttons on the “Main” sheet.. Buttons to sort numbers 1, 2 and 3.
• Button 1 is to sort the number # 1, Button 2 to sort #2 and Button 3 to sort #3 their respective sheets.

Details:

• Number sorting to be started by pushing a button to run the macro for the number labeled on that button.
• Sorts all draws (by the number of first ball) to the corresponding number sheet.
• Pushing the macro button again(twice) shouldn’t duplicate the numbers…
• After sorting, the last number in the Main list won’t have a P1 of P2 number on its corresponding number sheet yet.. not until I add another number to the bottom of main list and the macro for is run again. But running the macro again will cause duplicates to be sorted.
• Need to find a better way to write the macro, insteading of adding 60 individual macros, on for each number.
• Need to resolve the duplications issue.
  • Possibly deleting the draw from the “Main” sheet after it has been sorted, or
  • Add a column and putting a “x” next to the draw after it’s been sorted, then previous, old draws won’t be considered if the macro sees that “x” only new draws added which don’t have the “x

See the example mini sheets below explanation:

Clear out all draws from the “Main” sheet. As draws are entered, they should be sorted by the first balls number when the corresponding button is clicked.

Enter the first draw, 5/2/2015 1 10 11 12 13 1, starting with the draws date in A3, then Ball 1 in B3, Ball 2 in C3 then Balls 3, 4, 5 and 6 to D3, E3, F3 and G3: . So the first ball is the number 1. When button 1 is clicked, it should be sorted (copied) to sheet “#1” to the “All #1 Draws” section. There are no draws above (before) it, so nothing is entered in the M1 section (M2 through S2) or in the P1 or P2 sections yet because there are no draws after it either.

The next draw, 5/4/2015, ball 1 number is 2. So when button 2 is clicked, this draw is sorted or copied to Sheet #2 to the “All #2 Draws” section. On Sheet #2, you will see it there.. plus, since there is a draw before it (the 5/2/2015 draw). The 5/2 draw will also be copied to the M1 of sheet #2 section. Also, the 5/4 draw will be copied to the P1 (P1 stands for Plus 1) section on sheet #1 for the 5/2 draw. There is no draw on the P2 section yet because there hasn’t been 2 draws after the first draw.

The next draw, 5/7/2015, starts with the number 3. Clicking Button 3 will cause this draw to be copied to sheet #3 “All #3 Draws” Section. Plus, the 5/4 draw will be copied to the “M1” section. Also, this draw (the 5/7 draw) will be copied to the “P1” Section on the “#2” sheet for Draw 5/4 and also to the “P2” section on Sheet “#1” for the 5/2 draw.

If you paste all of the draws from the “draw pool” sheet over to the “Main” sheet and then run each of the macros for balls 1, 2 and 3, you will see a good example of how it’s suppose to work and what I want. If you then click on the buttons again, you will see the duplication occur, which I don’t want.

I hope this makes sense?? I’ve tried to explain how the Macro works as best I can.
I am having problems trying to resolve these issues… Please check it out and let me know how to fix the issues I’m having. I greatly appreciate any help offered. Thank you!!!

"Main" sheet

Example Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1
2
3	5/2/2015	1	10	11	12	13	1
4	5/4/2015	2	5	21	30	60	4
5	5/7/2015	3	11	44	47	53	2
6	5/11/2015	7	8	14	18	52	1
7	5/14/2015	6	28	34	39	43	2
8	5/18/2015	2	30	35	38	51	1
9	5/21/2015	18	22	24	29	56	4
10	5/25/2015	15	40	48	59	60	1
11	5/28/2015	1	25	28	33	47	1
12	6/1/2015	3	10	23	37	56	3
13	6/29/2015	1	19	37	49	56	3
14
Main

"#1" sheet

Example Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR
1		All #1 Draws												M1												P1												P2
2	5/2/2015	1	10	11	12	13	1																		5/4/2015	2	5	21	30	60	4						5/7/2015	3	11	44	47	53	2
3	5/28/2015	1	25	28	33	47	1						5/25/2015	15	40	48	59	60	1						6/1/2015	3	10	23	37	56	3						6/29/2015	1	19	37	49	56	3
4	6/29/2015	1	19	37	49	56	3						6/1/2015	3	10	23	37	56	3
5
#1

"#2" Sheet

Example Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT
1		All #2 Draws												M1												P1												P2
2	5/4/2015	2	5	21	30	60	4						5/2/2015	1	10	11	12	13	1						5/7/2015	3	11	44	47	53	2						5/11/2015	7	8	14	18	52	1
3	5/18/2015	2	30	35	38	51	1						5/14/2015	6	28	34	39	43	2						5/21/2015	18	22	24	29	56	4						5/25/2015	15	40	48	59	60	1
4
#2

"#3" Sheet

Example Question.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU	AV
1		All #3 Draws												M1												P1												P2
2	5/7/2015	3	11	44	47	53	2						5/4/2015	2	5	21	30	60	4						5/11/2015	7	8	14	18	52	1						5/14/2015	6	28	34	39	43	2
3	6/1/2015	3	10	23	37	56	3						5/28/2015	1	25	28	33	47	1						6/29/2015	1	19	37	49	56	3
4
#3

Example Question.xlsm
	A	B	C	D	E	F	G	H
1
2
3	5/2/2015	1	10	11	12	13	1
4	5/4/2015	2	5	21	30	60	4
5	5/7/2015	3	11	44	47	53	2
6	5/11/2015	7	8	14	18	52	1
7	5/14/2015	6	28	34	39	43	2
8	5/18/2015	2	30	35	38	51	1
9	5/21/2015	18	22	24	29	56	4
10	5/25/2015	15	40	48	59	60	1
11	5/28/2015	1	25	28	33	47	1
12	6/1/2015	3	10	23	37	56	3
13	6/4/2015	4	9	32	41	43	2
14	6/8/2015	33	39	43	49	52	3
15	6/11/2015	17	24	43	49	57	2
16	6/15/2015	10	14	16	32	34	3
17	6/18/2015	1	2	14	29	59	3
18	6/22/2015	5	26	27	51	59	2
19	6/25/2015	3	26	27	29	58	1
20	6/29/2015	1	19	37	49	56	3
21	7/2/2015	18	19	31	43	54	3
22	7/6/2015	3	10	30	33	40	1
23	7/9/2015	19	34	36	54	55	1
24	7/13/2015	5	23	27	55	57	1
25	7/16/2015	22	29	47	53	54	3
26
Draw pool for examples

Dave