Another Find and Copy Row help request

[3gswish]

Hi Everyone,

First let me start by saying that I am very much a newbie when it comes to writing VB and macros. I can look at a macro and pretty much figure out what it's doing, but adding to and modifying pretty much results in massive errors. If you suggest something, can you please be so kind as to include the full code?

I've been searching this site, and many others for a macro that will search a worksheet by row for a particular substring and copy that row only once! The substring may exist more than once in a particular cell, or in multiple cells of the same row.

The code that I've found elsewhere on this site and others seems to copy the row for every cell in that row that contains the string. For instance, if the string exists in row 5, column A and column C, I get the row copied twice in the destination sheet.

I think what would help is a way to tell the find to start on the next row once it finds and copies a row. Your suggestions are very much appreciated!

BTW - Thanks for all of the people that have submitted code and made it available to us all! Here is the macro I am currently using.

Sub Macro1()
Dim strLastRow As String
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet
Dim rngtest As String
Application.ScreenUpdating = False
Set wSht = Worksheets("Sheet1")
strToFind = InputBox("Enter Search Criteria")
With wSht.Range("A:C")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
strLastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
rngC.EntireRow.Copy Sheets("Sheet2").Cells(strLastRow, 1)
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <> FirstAddress
End If
End With
MsgBox ("Finished")
End Sub

 

[craig.penny]

I tried this

Sub testFind()
    Dim s As String, r As Range, rng As Range
    s = Application.InputBox("Enter String")
    Set rng = ActiveSheet.Range("A:C")
    Set r = rng.Find(s)
End Sub

and it's working for me. I put "abc" in column A and entered "abc" (the quotation marks were entered just as written here for both) in the InputBox and it was found. I'm not sure it'll help, but can you post more of your code?