PDA

View Full Version : Search and copy macro with search criteria



Richlilrich
06-13-2016, 07:59 AM
Hello I'm currently have a workbook with over 2000 lines of information.
What I'm trying to do is to search for a number in the B Column and copy the results to A2 in sheet2 going down A3 A4 A5 as there is numerious results.

The code below searchs copies and pastes but stops at the first one it finds and doesn't carry on for the rest.

Can anyone help please?


Sub Button5_click ()
Dim rId As Range, celS As Range, celT As Range
Dim wS As Worksheet, wT As Worksheet
Dim sId As String

Set wS = Worksheets("date1")
Set wT = Worksheets("Sheet1")
Set celT = wT.Range("A6")

Do

sId = InputBox("Enter week no")
If Len(sId) = 0 Then Exit Sub

Set rId = wS.Range("B3")
Set rId = wS.Range(rId, wS.Cells(wS.Rows.Count, rId.Column).End(xlUp)) 'rest of data

Set celS = rId.Find(sId, , xlValues, xlWhole, , , False)

If Not celS Is Nothing Then
Set celS = Intersect(wS.Columns("C:C"), celS.EntireRow)
If Not IsEmpty(celT) Then
Set celT = wT.Cells(wT.Rows.Count, celT.Column).End(xlUp).Offset(1)
End If
celT.Value = celS.Value
End If

Loop Until Len(sId) = 0

End Sub

mdmackillop
06-13-2016, 08:37 AM
Try filtering the number then copy and paste the results

Richlilrich
06-13-2016, 08:42 AM
By filtering I still would have to try and find the information by scrolling though lots of information formation this page will only build by another 2000 the person using this has never used excel or little experience so I want to make it as simple as possible

Many thanks,
Rich

mdmackillop
06-13-2016, 09:51 AM
I don't understand your last post.
Can you post a workbook with sample data and layout; show a desired result if possible. Use Go Advanced/Manage Attachments

Richlilrich
06-13-2016, 10:07 AM
https:/ /docs.google.com/spreadsheets/d/1mJmGIO0Xvh21F3hngYIUNSTaEbhBRk2QoDpb5tlf39w/edit?usp=drive_web

Here is a quick view of the sheets I have no interest etc access at work only though my mibile

Richlilrich
06-13-2016, 10:18 AM
I'll try and explain better sorry about this.

Whatever data I put into the input box it searches it searches column "b" and when it finds a result it copies the name in column "c" then pastes it into the new sheet which is what it does. But it stops on the first one it finds when there are multipule results.

mdmackillop
06-13-2016, 11:14 AM
Can you post your file using Go Advanced/Manage Attachments to provide a working version.

Richlilrich
06-13-2016, 11:37 AM
16377

Richlilrich
06-13-2016, 11:48 AM
The link would be a better to get an understanding

mdmackillop
06-13-2016, 12:23 PM
Here is a formula solution for the first field. Others can be adapted from this.

Richlilrich
06-13-2016, 03:26 PM
Many Thanks,

seems like what i use to use on the Weeks part of the workbook however the reason i'm trying to switch to a macro is because the amount of times someone has deleted a formula and saved the document and the next person using it can't use it. I would love to use this do you know a way to protect just that section of the workbook?

also i'm struggling how you got this formula to search the Richie Odds part without inputting 'Richie Odds'!
unfortunately my excel skills are from trolling online until i find something similar and trying to tweek it to my needs

mdmackillop
06-13-2016, 03:53 PM
I created two range names "Odds" for Ritchie Odds column B and OddsData for Columns B:C. You would need to add similar names for each sheet and substitute into the formula