Want Macro to place answers on next sheet NEED HELP

L

larbec

Member
I have this macro that finds data searching rows. I have to hit next for it to find the next set if same data I would like the ability for it to paste all the data it finds on the next sheet

Currently I have to stop the search to copy and paste data and then start all over again. Plus it is VERY time consuming to look at patterns and as y'all know time between draws is crucial for yes to have our data like now lol

Here is the macro and I'll upload a spreadsheet soon. I'm on my iPad and not sure I can from here

Sub test() Dim myPtn As Range, r As Range, x, myTxt, mymatch As Range
Dim ff As String, i As Long, y, flg As Boolean, myAreas As Areas
Dim Match
Columns("c:e").Borders.LineStyle = xlNone
Columns(6).ClearContents
Set myAreas = Application.InputBox("Select the pattern range(s)", Type:=8).Areas
For Each myPtn In myAreas
myTxt = myPtn(1).Value
Set r = Columns(3).Find(myTxt, , , 1)
If Not r Is Nothing Then
ff = r.Address
Do
x = Evaluate(r.Resize(myPtn.Rows.Count, 2).Address & "=" & myPtn.Address)
For i = 1 To 2
y = Filter(Application.Transpose(Application.Index(x, 0, i)), False)
If UBound(y) <> -1 Then flg = True: Exit For
Next
If Not flg Then
If mymatch Is Nothing Then
Set mymatch = r.Resize(myPtn.Rows.Count, 3)
Else
Set mymatch = Union(mymatch, r.Resize(myPtn.Rows.Count, 3))
End If
r.Resize(myPtn.Rows.Count, 3).BorderAround Weight:=xlThick
r.Offset(, 3).Value = "x"
End If
Set r = Columns(3).FindNext(r): flg = False
Loop Until ff = r.Address
End If
Next
MsgBox IIf(mymatch Is Nothing, "No match", Replace(mymatch.Address, ",", vbLf)), _
, IIf(mymatch Is Nothing, "Not ", "") & "Found"
If Not mymatch Is Nothing Then mymatch.Select

End Sub



VB:
Private Sub CommandButton1_Click()
Dim c
Dim firstAddress As String
With Worksheets("Filter").Range("F2", Range("F" & Rows.Count).End(xlUp))
Set c = .Find("x", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, -3).Resize(1, 3).Select
Set c = .FindNext(c)
If MsgBox("Next Match?", vbYesNo) = vbNo Then Exit Sub
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
 
L

larbec

Member
Here is a link for an example spreadsheet


https://onedrive.live.com/redir?resid=ABA6DC5F6B4EABEB!924&authkey=!AKB9ks5snepVBz8&ithint=file%2cxlsm
 
Frank

Frank

Member
I not sure whether I can help here because your macro contains some commands I haven't used previously. I thought looking at your file might help me get my head around it but Microsoft say I can't see your file because you've either deleted it or I don't have permission to see it. :confused:
 
Frank

Frank

Member
Yes i can download it. Ive had a quick look, it raises quite a few questions though. I'm off to bed, I'll see if I get chance to look further over the weekend. More information about what data you are searching for, what is expected in terms of input to the form (example input please) and what currently happens after you enter it would be a big help.

Also the term VB: before the commandbutton sub is not acceptable in a module, so what were you trying to convey by showing it ? That sub refers to a worksheet called "Filter" ...it is being accessed by the macro,

With Worksheets("Filter").Range("F2", Range("F" & Rows.Count).End(xlUp))

but the worksheet does not exist in your example so cannot work ?

Thanks
 
L

larbec

Member
Wow!, my apologies Frank, for some reason the file did not upload or download correctly so I can see why you are lost LOL, I would be too. Here is another link and this will make a lot more sense to you I am sure

https://onedrive.live.com/redir?resid=ABA6DC5F6B4EABEB!928&authkey=!AHWmXiSuTMBUiOA&ithint=file%2cxlsm
Click to expand...


Instructions:

When you open the file you will see under column C the numbers 2 10 2 in rows C:2 C:3 C:4. I am looking for all the number sequences down column C that have the 2 10 2 in them

Click on the button labeled "Find Patterns"
That brings up a box that says select range(s) you will high lite C:2 to E:2 and drag down to C:4 E:r and then press :eek:k"

This will begin the search. When it finds the answers it will show you a location that the next set of numbers ( 2 10 2) are in $C$17:$E$22
click on "ok" this brings you to that location

Now, for what I want I need help with. I want to be able for the Macro to copy and paste all the locations it finds and place them on the next sheet with 10 rows above and 10 rows below (info from B:J)

If this can not be done easily then I would like the ability to pause the search and copy the location to another sheet manually and then start where it left off. Presently every-time I go to copy and paste, I have to stop it and start all over again. Ideal would be to copy and paste to the next sheet which will save an enormous amount of time

This should make better sense LOL, sorry for the confusion and wasted time
 
Frank

Frank

Member
Thanks Larbec for tring to clarify the situation. I say trying because Microsoft again say "This item might have been deleted, expired or you might not have permission to view it. Contact the owner of this item for more information."

Has anyone else tried to download it with the same results ? Theres a lot to be said for mediafire, it just works ..:rolling:

If you can get an accurate copy of the file to download for others I will look at your detailed instructions and try and make sense of it. :)
 
L

larbec

Member
My apologies again darn, I am at our cafe and having issue uploading to box and dropbox, will be hoe shortly and will open mediafile or try it real quick, will have it up soon
 
Frank

Frank

Member
Yes this time its downloaded as a full spreadsheet with macros. I'll take a look when I have time, but Christmas is roaring up, I won't have too much time, so we shall see ..:)
 
Frank

Frank

Member
More questions... its not making total sense at the moment. In the example you quote it finds two sequences - not one, C:17 to C:22 and then highlights the adjacent cells in column D and E that go with them.

You would want to copy cells C17:J22 onto the sheet called "Patterns" ?

Leaving 10 rows where ? Do you mean paste on row 32 of the patterns sheet ?

I'm not getting the "start all over again" bit. Are you saying its not finished its task after you click OK and it is still searching ? I'm not seeing that.

The search macro is stopped anyway isn't it, after "OK" ? Are you expecting it to continue to look for more sequences of 2,10,2 BELOW row 22 , or wait for you to click the NEXT button ?

I know there is another button (NEXT) linked to another spreadsheet, which doesn't work because that other spreadsheet isnt present. Is that (normally) supplying the sequence to look for in cells C2:C4 when you click it ?

By start all over again do you mean click NEXT to find the next sequence, but first you have to click (the next button) and get the first one you've already done, go through the motions of finding one youve already done, click next again until you search for a different sequence ?

I'm puzzled because clicking OK at the moment stops the macro anyway, so what is continuous about the macro now, that wouldn't be if you copied and pasted manually ? I'm not seeing the full picture I dont think.
I'm also intrigued by the 'x' pasted in cell G2 which cannot be deleted, unless you run the search and deliberately crash it with the wrong data. :confused:
 
L

larbec

Member
You would want to copy cells C17:J22 onto the sheet called "Patterns" ?

No, I want to copy and paste whatever data I ask for in C:2 through C:12. In the example I am asking it to search all the 2 10 2 and it found them in C:17 and C:20. I have approx 100,000 rows I will be looking for the 2 10 2 in C. All that it finds I want to place on Patterns sheet along with 10 rows above and below what it finds


Leaving 10 rows where ? Do you mean paste on row 32 of the patterns sheet ? What ever data it finds in column C (all 100,000 rows) it will past them on the sheet called Patterns along with 10 rows above and below what it finds. So, if it finds them in C:17, I want to copy and past C& to C:29 to the Pattens sheet

I'm not getting the "start all over again" bit. Are you saying its not finished its task after you click OK and it is still searching ? I'm not seeing that.

Lets say I do a search and it finds the 1st group like it did in C:17 and 2nd group in C:20. If I want to copy this manually I would have to click on "no" and not advance to the next group of 2 10 2. Imagine finding a group of 2 10 2 say in row 25,000. In order for me to copy and paste what I find along with 10 rows above and below, I have to stop the Macro to do so. Now after I paste it I can not start where I left off at row 25,000. I have to go all the way back to row 13 and start the search sequence all over again

I'm not seeing that. Go through the Instructions and when it finds C:17 copy and paste it to the patterns sheet and you will see what I mean. You can not do it unless you stop the macro


The search macro is stopped anyway isn't it, after "OK" ? Are you expecting it to continue to look for more sequences of 2,10,2 BELOW row 22 , or wait for you to click the NEXT button ?

In this example C only goes to row 34, I have over 100,000 rows to search on my main sheets so I will be hitting next a lot



I know there is another button (NEXT) linked to another spreadsheet, which doesn't work because that other spreadsheet isnt present. Is that (normally) supplying the sequence to look for in cells C2:C4 when you click it ? If you view code you should see it, its in this workbook. Here it is

Private Sub CommandButton1_Click()
Dim c
Dim firstAddress As String
With Worksheets("Filter").Range("F2", Range("F" & Rows.Count).End(xlUp))
Set c = .Find("x", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.Offset(, -3).Resize(1, 3).Select
Set c = .FindNext(c)
If MsgBox("Next Match?", vbYesNo) = vbNo Then Exit Sub
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub


Next will advance to the next set or group of numbers (2 10 2) it finds in C



I'm puzzled because clicking OK at the moment stops the macro anyway, so what is continuous about the macro now, that wouldn't be if you copied and pasted manually ? I'm not seeing the full picture I dont think.
I'm also intrigued by the 'x' pasted in cell G2 which cannot be deleted, unless you run the search and deliberately crash it with the wrong data.
Reply With Quote

Don't be puzzled, its there for a reason LOL copy write is everything in life

Thanks for trying to help
 
Frank

Frank

Member
Thanks Larbec for your reply, but I'm busy with Christmas right now. I'll get back to you later ........ :santa:
 
L

larbec

Member
Just a heads up. I changed how I wanted this to look and wanted to add so e extra features. Whoever said we need much sleep anyway lol.

I sm almost completed with this Macro and it's working great. I have a couple of minor things I need to work out Thanks for replying and keep winning $$$$
 
Frank

Frank

Member
Well I'm pleased you found time to sort this out over the Christmas week, I've barely been on line this week. Its always better to sort these things out yourself anyway, then you know how it works. :liplick:
 
L

larbec

Member
so True Frank!! I have a few Macros I have written to different things if you ever need anything feel free to ask, for example one will search for all pairs so if the draw is 6-15-22-35-42-50 and you want to find all pairs that go with 22-35. Different things
 
Frank

Frank

Member
Thanks Larbec, but I prefer to write my own stuff. I've forgotten more spreadsheets and macros over the past 20 years that do wierd and wonderful stuff! I sometimes find a spreadsheet and wonder why I wrote it, and what to do with it, invariably it was to answer some question someone asked in a forum donkeys years ago about some obscure statistic long forgotten about. :rolling:
Now I'm in retirement I rarely dig out the old stuff.
 
You must log in or register to reply here.

Sidebar

Top