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

dwrowe001

New Member
Joined
Mar 12, 2017
Messages
42
Office Version
  1. 2016
Platform
  1. Windows
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
ABCDEFGHIJKLMNOPQ
1
2
35/2/20151101112131
45/4/2015252130604
55/7/20153114447532
65/11/2015781418521
75/14/20156283439432
85/18/20152303538511
95/21/201518222429564
105/25/201515404859601
115/28/20151252833471
126/1/20153102337563
136/29/20151193749563
14
Main


"#1" sheet
Example Question.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQAR
1All #1 DrawsM1P1P2
25/2/201511011121315/4/20152521306045/7/20153114447532
35/28/201512528334715/25/2015154048596016/1/201531023375636/29/20151193749563
46/29/201511937495636/1/20153102337563
5
#1


"#2" Sheet
Example Question.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASAT
1All #2 DrawsM1P1P2
25/4/20152521306045/2/201511011121315/7/201531144475325/11/2015781418521
35/18/201523035385115/14/201562834394325/21/2015182224295645/25/201515404859601
4
#2


"#3" Sheet
Example Question.xlsm
ABCDEFGHIJKLMNOPQRSTUVWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAUAV
1All #3 DrawsM1P1P2
25/7/201531144475325/4/20152521306045/11/20157814185215/14/20156283439432
36/1/201531023375635/28/201512528334716/29/20151193749563
4
#3


Example Question.xlsm
ABCDEFGH
1
2
35/2/20151101112131
45/4/2015252130604
55/7/20153114447532
65/11/2015781418521
75/14/20156283439432
85/18/20152303538511
95/21/201518222429564
105/25/201515404859601
115/28/20151252833471
126/1/20153102337563
136/4/2015493241432
146/8/201533394349523
156/11/201517244349572
166/15/201510141632343
176/18/2015121429593
186/22/20155262751592
196/25/20153262729581
206/29/20151193749563
217/2/201518193143543
227/6/20153103033401
237/9/201519343654551
247/13/20155232755571
257/16/201522294753543
26
Draw pool for examples


Dave
 

Some videos you may like

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Watch MrExcel Video

Forum statistics

Threads
1,126,986
Messages
5,621,991
Members
415,872
Latest member
ReignEternal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top