Find search string and copy row to another sheet (1 Viewer)

mailtec · Aug 10, 2006

hi all,

I'm working on a VBA script for Excel that i need some help on. i'm familiar with programming vb applications, but not in excel.

I have an excel file with about 24,000 rows and 50 columns. I need to search for a unique value in one of the columns, then copy that row to the next sheet.

I would like to have a form that has a text box to enter the search string into, and a button that will find the row in sheet1, then copy and paste it into sheet2. Then the user can enter the next string, click the button, and the found row from sheet2 would be copied to the next empty row in sheet2. and so on.

I'm currently trying some bits and pieces of similar code examples i found online, but haven't gotten it to do what i want yet. i'm open to doing this in Access if it would be any easier since i'm familiar with SQL.

any help would be appreciated.

shades · Aug 11, 2006

Howdy, and welcome to the board.

Perhaps as a starting point, turn on the Macro recorder and find the only row and copy and paste. Once you get that, then we can help you design a loop to allow that. And a simple Input Box will allow the user enter a string to search.
________
MERCURY PARK LANE PICTURE

mailtec · Aug 11, 2006

thanks for the reply,

here is the macro i recorded:

Code:

    Cells.Find(What:="516916", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
    Rows("179:179").Select
    Range("J179").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste

and this is what i'm working with on the form right now:

Code:

Private Sub btnFind_Click()
Dim strSic As String
strSic = Me.txtSic.Text

    Cells.Find(What:=strSic, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

'need to figure out how to dynamically select the row that has the found string and paste in the next available row instead of the static numbers here

    Rows("179:179").Select
    Range("J179").Activate
    Selection.Copy
    Sheets("Sheet2").Select
    ActiveSheet.Paste
    Sheets("Sheet1").Select
    Range(A1).Select
End Sub

shades · Aug 11, 2006

Will the string appear more than once? Do you want each row copied that has it?
________
Free joomla themes

shades · Aug 11, 2006

Okay, as a first step, I have assumed that Column A is the target (although not necessary).

Code:

Sub test()
    Dim lngNextRow As Long
    Dim strMyString As String
    Sheets("Sheet1").Activate
    strMyString = InputBox("Enter the number you wish to find")
    Cells.Find(What:=strMyString, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext).Activate

    ActiveCell.EntireRow.Copy
    
    Sheets("Sheet2").Select
    lngNextRow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lngNextRow).PasteSpecial
End Sub

Now, this is not anywhere complete for what you want. But try this. It will activate Sheet1 to be sure you start at the correct place, then find the string. The way it is set up now, it will begin searching from the cell where your cursor is set. So that will have to be refined. But when it finds that string it will copy that row to the next available row on Sheet2.

Note that I use the Inputbox to get the string.
________
Franco cortese

mailtec · Aug 11, 2006

haha, wow thats great. I had just finished writing this nasty chunk of code that worked (but a tad over complicated

).

Code:

    Cells.Find(What:=strSic, After:=[A1], LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
    Dim strRowval As String
    intRowval = Selection.Row
    Rows(intRowval & ":" & intRowval).Select
    Range("A" & intRowval).Activate
    Selection.Copy
       
    Sheets("Sheet2").Select
    
    '--------------------------
    'Find last row code
    'Source: http://www.ozgrid.com/VBA/ExcelRanges.htm
    '--------------------------
        Dim LastRow As Integer
    If WorksheetFunction.CountA(Cells) > 0 Then
        'Search for any entry, by searching backwards by Rows.
        LastRow = Cells.Find(What:="*", After:=[A1], _
              SearchOrder:=xlByRows, _
              SearchDirection:=xlPrevious).Row
    End If
    
    
    Dim strPosition As String
    LastRow = LastRow + 1
    strPosition = "A" & LastRow
    Range(strPosition).Activate
    ActiveSheet.Paste
        
    Sheets("Sheet1").Select
    'frmFind.SetFocus
    'Me.txtSic.Text.Clear

now yours does the same thing, only a little simpler. haha.

now my questions:

edit: i only want to search for the string in column "AY", can we do this with the find method?
how do i clear my textbox on the form and set the focus after this code so another entry can be entered?
also, if the search string is in the sheet more than one time, how hard would it be to copy all rows that it is found in?

shades · Aug 11, 2006

mailtec said:
now my questions:

edit: i only want to search for the string in column "AY", can we do this with the find method?
how do i clear my textbox on the form and set the focus after this code so another entry can be entered?
also, if the search string is in the sheet more than one time, how hard would it be to copy all rows that it is found in?

Yes, we can restrict where to search, perhaps using a different method.

By using the Inputbox, you wouldn't need to clear.

If it appears more than one time, the code will take care of that, especially if we use a loop.

I have been very busy at work today, so haven't worked on it since early this morning.
________
Vaporizer Pipe

mailtec · Aug 17, 2006

thanks for all your help on this. i just got back from vacation and starting to work on it again.

the input box you suggested works great and does away with the other stuff i didn't need.

can we tell the find method where to look using the LookIn, LookAt, or After parameters?

and about the loop, how about using something like "find next"? or is there a better way to do this?

shades · Aug 17, 2006

Unfortunately, I will be traveling for work most of the next three weeks, and I am swamped with work right now (until I leave). Perhaps someone else can help you with the next step. Sorry about that.
________
MERCEDES-BENZ F700

shreyash · Aug 18, 2006

You might find this piece of code useful:

Moving the Search Results to a Separate Page
This code sample searches the columns of a worksheet for the occurrence of a word ("Hello"). Once matching data is found, it is copied to another worksheet ("Search Results").

Sub FindMe()
Dim intS As Integer
Dim rngC As Range
Dim strToFind As String, FirstAddress As String
Dim wSht As Worksheet

Application.ScreenUpdating = False

intS = 1
'This step assumes that you have a worksheet named
'Search Results.
Set wSht = Worksheets("Search Results")
strToFind = "Hello"

'Change this range to suit your own needs.
With ActiveSheet.Range("A1:C2000")
Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
If Not rngC Is Nothing Then
FirstAddress = rngC.Address
Do
rngC.EntireRow.Copy wSht.Cells(intS, 1)
intS = intS + 1
Set rngC = .FindNext(rngC)
Loop While Not rngC Is Nothing And rngC.Address <>
FirstAddress
End If
End With

End Sub

Source: http://msdn.microsoft.com/library/d...c_xl2003_ta/html/OfficeTipsWhenUsingExcel.asp

mailtec · Aug 18, 2006

yes shreyash, this is exactly what i needed. i remember trying this code when i first started this project, but i didn't know enough about excel vba to get it to do what i wanted.

this is what i'm using as of now and it looks like its doing just what i need. i'll test it more when i get back in the office.

Code:

Private Sub btnFind_Click()

    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("Sheet2")
    strToFind = InputBox("Enter the SIC code to find")
    
    With ActiveSheet.Range("AY1:AY23331")
        Set rngC = .Find(what:=strToFind, LookAt:=xlPart)
            If Not rngC Is Nothing Then
                FirstAddress = rngC.Address
                Do
                    strLastRow = Worksheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row + 1
                    rngC.EntireRow.Copy wSht.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

if anyone sees a bug in this logic, feel free to point it out. thanks for the help!

Find search string and copy row to another sheet (1 Viewer)

mailtec

New member

shades

Registered User.

mailtec

New member

shades

Registered User.

shades

Registered User.

mailtec

New member

shades

Registered User.

mailtec

New member

shades

Registered User.

shreyash

Guest

mailtec

New member

Similar threads

Users who are viewing this thread