[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Excel macro to find text and copy the full line

Posted on 2007-07-26
9
Medium Priority
?
3,619 Views
Last Modified: 2008-01-09
Hi,

I need a macro Which will ask me to find data in the excel.When i typoe the data in the box it needs to search the data and copy the data to a new sheet.It should continue until i click cancel.All found data in 1 sheet.

regards
Sharath
0
Comment
Question by:bsharath
  • 5
  • 4
9 Comments
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19574924
Hi Sharath,

Do you mean something like this....

    Sub FindAndCopy()
        Dim SourceSheet As Worksheet: Set SourceSheet = ActiveSheet
        Dim NewSheet As Worksheet: Set NewSheet = Worksheets.Add
        Dim FindStr As String
        Dim FindCell As Range
        Do
            FindStr = InputBox("Enter Text to find.", "Enter Text")
            If FindStr <> "" Then
                Set FindCell = SourceSheet.Cells.Find(FindStr, , xlValues, xlWhole)
                If Not FindCell Is Nothing Then
                    FindCell.Copy NewSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                Else
                    MsgBox FindStr & " not found."
                End If
            End If
        Loop While FindStr <> ""
    End Sub

Regards,

Wayne
0
 
LVL 11

Author Comment

by:bsharath
ID: 19575051
This works great.

Is this case sensitive.
I want the full row to be copied if the data is found.
What happens if i type Sharath and the sheet has as SHARATH,SHarath,sharath
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19575137
bsharath,

This one will copy the entire row of data to the new sheet....

     Sub FindAndCopy()
         Dim SourceSheet As Worksheet: Set SourceSheet = ActiveSheet
         Dim NewSheet As Worksheet: Set NewSheet = Worksheets.Add
         Dim FindStr As String
         Dim FindCell As Range
         Do
             FindStr = InputBox("Enter Text to find.", "Enter Text")
             If FindStr <> "" Then
                 Set FindCell = SourceSheet.Cells.Find(FindStr, , xlValues, xlWhole)
                 If Not FindCell Is Nothing Then
                     FindCell.EntireRow.Copy NewSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                 Else
                     MsgBox FindStr & " not found."
                 End If
             End If
         Loop While FindStr <> ""
     End Sub

It is not case sensitive. If you wish for it to be case sensitive, replace this line....

     FindCell.EntireRow.Copy NewSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

....with these lines....

     If FindCell.Value = FindStr Then
         FindCell.EntireRow.Copy NewSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
     Else
         MsgBox FindStr & " not found."
     End If

Wayne

Wayne
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 11

Author Comment

by:bsharath
ID: 19575249
If there are 10 contents as the same
Ex:
Server is there in 10 rows.
Then what happens
0
 
LVL 48

Expert Comment

by:Wayne Taylor (webtubbs)
ID: 19575267
bsharath,

Only the first one is copied.

Wayne
0
 
LVL 11

Author Comment

by:bsharath
ID: 19575268
I want all the server names to be copied.If found the same name in more than one then all data has to be copied.
0
 
LVL 48

Accepted Solution

by:
Wayne Taylor (webtubbs) earned 2000 total points
ID: 19575352
bsharath,

This one copies the entire row, is case sensitive, and will copy all found data.....

     Sub FindAndCopy()
         Dim SourceSheet As Worksheet: Set SourceSheet = ActiveSheet
         Dim NewSheet As Worksheet: Set NewSheet = Worksheets.Add
         Dim FindStr As String
         Dim FindCell As Range
         Do
             FindStr = InputBox("Enter Text to find.", "Enter Text")
             If FindStr <> "" Then
                 Dim frstAdd As String
                 Set FindCell = SourceSheet.Cells.Find(FindStr, , xlValues, xlWhole)
                 If Not FindCell Is Nothing Then
                     frstAdd = FindCell.Address
                     Do
                         If FindCell.Value = FindStr Then
                             FindCell.EntireRow.Copy NewSheet.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
                         End If
                         Set FindCell = SourceSheet.Cells.FindNext(FindCell)
                     Loop While Not FindCell Is Nothing And FindCell.Address <> frstAdd
                 Else
                     MsgBox FindStr & " not found."
                 End If
             End If
         Loop While FindStr <> ""
     End Sub

Wayne
0
 
LVL 11

Author Comment

by:bsharath
ID: 19575445
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

872 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question