Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 251
  • Last Modified:

Any way to search multiple text in an excel sheet

Hi,

I have say 10 names.What i want is i search all the 10 names and a macro to copy all the results next to it to a new page.

Sharath
suresh
ramesh
Karthik
etc.

Search 10 names and copy the full line to a new sheet.

Regards
Sharath
0
bsharath
Asked:
bsharath
  • 12
  • 10
1 Solution
 
hiteshgoldeneyeCommented:
Sub a(RequiredValue as String)
Dim s2row
For i=1 to Sheet1.UsedRange.Rows.Count
    If Sheet1.Cells(i,1) = RequiredValue then
        ws1.Rows(i).Copy Destination:=ws2.Rows(s2row)
        s2row= s2row +1
    End Sub

Next
End Sub
0
 
bsharathAuthor Commented:
Where should i put all the 10 names for it to search.Can you please give a name for the Macro
0
 
hiteshgoldeneyeCommented:
Sub searchvalue (RequiredValue as String)
Dim s2row
For i=1 to Sheet1.UsedRange.Rows.Count
    If Sheet1.Cells(i,1) = RequiredValue then
        ws1.Rows(i).Copy Destination:=ws2.Rows(s2row)
        s2row= s2row +1
    End Sub

Next
End Sub

you can use this macro but you will have to invoke it 10 times, if this is not acceptable let me know
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
bsharathAuthor Commented:
Any other way because i may have 100's some times...
0
 
hiteshgoldeneyeCommented:
if you put the names to be searched in Sheet3 then the following macro should do the trick
Sub searchvalue()
Dim s2row
For i = 1 To Sheet1.UsedRange.Rows.Count
    For j = 1 To Sheet3.UsedRange.Rows.Count
        If Sheet1.Cells(i, 1) = Sheet3.Cells(j, 1) Then
            Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
            s2row = s2row + 1
         End If
    End If
Next
End Sub

Let me know if any problem
0
 
bsharathAuthor Commented:
For the first time the macro runs ok.Then again after i make any changes in the txt file and run the macro no changes happen.
0
 
hiteshgoldeneyeCommented:
>txt file, is this comment for this question or for the other one?
0
 
bsharathAuthor Commented:
For this macro.I get this error.

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:

End If without block If
---------------------------
OK   Help  
---------------------------
0
 
hiteshgoldeneyeCommented:
Sorry about that vuddy made a mistake, use the macro below
Sub searchvalue()
Dim s2row
For i = 1 To Sheet1.UsedRange.Rows.Count
    For j = 1 To Sheet3.UsedRange.Rows.Count
        If Sheet1.Cells(i, 1) = Sheet3.Cells(j, 1) Then
            Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
            s2row = s2row + 1
         End If
    Next
Next
End Sub
0
 
bsharathAuthor Commented:
I get this.

---------------------------
Microsoft Visual Basic
---------------------------
Can't execute code in break mode
---------------------------
OK   Help  
---------------------------

I have 100 names in Sheet 1Where should i put in the 10 names i need to search?
0
 
hiteshgoldeneyeCommented:
Put the 10 names to search in sheet3 column A
The results of the macro will be in sheet2
Sub searchvalue()
Dim s2row
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
    For j = 1 To Sheet3.UsedRange.Rows.Count
        If Sheet1.Cells(i, 1) = Sheet3.Cells(j, 1) Then
            Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
            s2row = s2row + 1
         End If
    Next
Next
End Sub
0
 
bsharathAuthor Commented:
If the names in Sheet 1 are in different rows other than A .Will it not search.Is there a way for the macro to search the whole page.Or if i can specify the colum to search
0
 
hiteshgoldeneyeCommented:
Sub searchvalue()
Dim s2row
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
   For j=1 To Sheet1.UsedRange.Columns.Count

      For j = k To Sheet3.UsedRange.Rows.Count
         If Sheet1.Cells(i, j) = Sheet3.Cells(k, 1) Then
              Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
              s2row = s2row + 1
         End If
      Next
   Next
Next
End Sub
0
 
bsharathAuthor Commented:
I get this.

---------------------------
Microsoft Visual Basic
---------------------------
Can't execute code in break mode
---------------------------
OK   Help  
---------------------------
0
 
hiteshgoldeneyeCommented:
Ok use this for searching the whole page
Sub searchvalue()
Dim s2row
s2row = 1
For i = 1 To Sheet1.UsedRange.Rows.Count
   For j = 1 To Sheet1.UsedRange.Columns.Count

      For k = 1 To Sheet3.UsedRange.Rows.Count
         If Sheet1.Cells(i, j) = Sheet3.Cells(k, 1) Then
              Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
              s2row = s2row + 1
         End If
      Next
   Next
Next
End Sub
0
 
bsharathAuthor Commented:
Great this worked.Any way to specify the macro to search only 1 particular colum
As when i search for emp id's it has 2 places where this is found.

Thanks a lot...
0
 
hiteshgoldeneyeCommented:
>to search only 1 particular colum
As when i search for emp id's it has 2 places where this is found.

i did not understand this point, could you explaint it more clearly?
0
 
bsharathAuthor Commented:
Any way we can only allow the macro to search in 1 colum if found copy the whole line to a new sheet
0
 
hiteshgoldeneyeCommented:
Ok use this for searching the column
Sub searchvalue()
Dim s2row
s2row = 1
j = 1 'For Column A - Chnage this to 2 for column B and so on
For i = 1 To Sheet1.UsedRange.Rows.Count
      For k = 1 To Sheet3.UsedRange.Rows.Count
         If Sheet1.Cells(i, j) = Sheet3.Cells(k, 1) Then
              Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row)
              s2row = s2row + 1
         End If
      Next
Next
End Sub
0
 
hiteshgoldeneyeCommented:
to administrator
shouldnt this question be in Microsoft Excel?
0
 
hiteshgoldeneyeCommented:
And sharath please put such questions in MS Excel Zone only
0
 
bsharathAuthor Commented:
Sure Hitesh
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 12
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now