search a string and copy rows thru vba

**promoboy2** · 12-02-2010, 11:51 AM

I want to search sheet2 for some word or number that i put in a textbox on sheet1 and copy the rows from sheet2 to sheet1. In any colum.

i had found a code and adjusted that, but it does not the copying and pasting.

could anyone help me please
here is the code:

    Sub SearchForString()
    Sheets("sheet1").Range("A18:J50000").ClearContents
    Range("A18").Select
    
        Dim LSearchRow As Integer
        Dim LCopyToRow As Integer
                

        On Error GoTo Err_Execute
        
        'Start search in row 2
        LSearchRow = 2

        'Start copying data to row 18 in sheet1 (row counter variable)
        LCopyToRow = 18
        Sheets("sheet2").Select
        While Len(Range("A" & CStr(LSearchRow)).Value) > 0

            'If value in column C = "", copy entire row to sheet1
            If Range("c" & CStr(LSearchRow)).Value = Sheets("sheet1").Textboxing.Value Then
        

                'Select row in sheet2 to copy
                Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
                Selection.copy

                'Paste row into sheet1 in next row
                
                Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
                Sheets("sheet1").Select
                ActiveSheet.Paste

                'Move counter to next row
                LCopyToRow = LCopyToRow + 1

                'Go back to sheet2 to continue searching
                Sheets("sheet2").Select

            End If

            LSearchRow = LSearchRow + 1

        Wend

        'Position on cell A18
        Application.CutCopyMode = False
        Sheets("sheet1").Select
        Range("A18").Select

        MsgBox "All matching data has been copied."

        Exit Sub

Err_Execute:
        MsgBox "An error occurred."

    End Sub

**promoboy2** · 12-03-2010, 04:29 AM

Originally Posted by davesexcel

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

Here is a attachment with the xls file
you see a add button to add data to the Articledata sheet and a textbox for looking up data.
I added some radio button to select the search criteria (but this is not yet active, because i don't know how to program it)

Its a sort of google for this file i won't

thanks for looking in

**DanExel** · 12-06-2010, 06:37 AM

Only problem was that the code was trying to match TEXT from the textbox with a value on your sheet2.
See attached spreadsheet. You will notice that all I changed was .Value to .Text
I also added Application.Screenupdating=False to make your macro run "smoother"
If you need help with the coding behind the rario buttens, just shout.

Regards:
Dan

**promoboy2** · 12-06-2010, 10:42 AM

Originally Posted by DanExel

Only problem was that the code was trying to match TEXT from the textbox with a value on your sheet2.
See attached spreadsheet. You will notice that all I changed was .Value to .Text
I also added Application.Screenupdating=False to make your macro run "smoother"
If you need help with the coding behind the rario buttens, just shout.

Regards:
Dan

thanks for looking in to it

I still have some questions:

1) when i search for the string "7254" i get only 1 row instead of 4 rows

2) is it possible to search for a range (A2:J65000) instead of 1 collum, or must i write as much macro's as rows i want to search at.

3) my previous question has something to do with the radio buttons, how do i get them to work?

**DanExel** · 12-07-2010, 03:12 AM

HI,
The statement : "Sheets("Gegevens Inbrengen").Select" was slightly out of place.
Find attachment with correction.
Working on your next problem.
Regards
Dan

**DanExel** · 12-07-2010, 04:35 AM

Hi
Here is version 3.
It solve your multi collumb search.(Question 2)
Note the "OR" statements.
Working on your 3rd question
Regards
Dan

**promoboy2** · 12-07-2010, 12:04 PM

Originally Posted by DanExel

Hi
Here is version 3.
It solve your multi collumb search.(Question 2)
Note the "OR" statements.
Working on your 3rd question
Regards
Dan

I just tried your last version and works fantastic.

can't wait to see the radio buttons working.

thanks for all your fine programming!

**DanExel** · 12-10-2010, 06:20 AM

At last, here is your final vesrion.
I opted for a Combo box rather than Radio buttens, but it does the same job.
Hope it will meet your requierments.

Regards
Dan

**davesexcel** · 12-10-2010, 07:46 AM

Originally Posted by DanExel

At last, here is your final vesrion.........

Hi Dan,
Are you missing the Named range "LocationList" in your example?

**promoboy2** · 12-10-2010, 09:35 AM

Originally Posted by DanExel

At last, here is your final vesrion.
I opted for a Combo box rather than Radio buttens, but it does the same job.
Hope it will meet your requierments.

Regards
Dan

I want to thank Dan for his work, this is what i wanted.

regards promoboy2

solved

**DanExel** · 12-12-2010, 04:51 AM

Hi Daves,
Do I have your name correct?
Yes it seems like I lost it (probably deleted it)
Dan

**davesexcel** · 12-12-2010, 08:44 AM

Originally Posted by DanExel

Hi Daves,
Do I have your name correct?
Yes it seems like I lost it (probably deleted it)
Dan

Your userform.show will error without the range LocationList

For Each cLoc In ws.Range("LocationList")
  With Me.cboLoc
    .AddItem cLoc.Value
  End With
Next cLoc

Without the range how can it load the userform?

**promoboy2** · 12-12-2010, 01:03 PM

Hi Dan,

I have adjusted the file a little bit and occurred an error what i can't solve.

when i choose ALL there is no problem, but when i choose something else it gives an error.

can you look into it please?

and i am also wondering if it is possible to look for part of string using the Cstr function?
(for looking to a part of a string) something like this "looking" when i give "look" in textbox it copys that row. (but only for the collum D "omschrijving") with the other collums its not necessary.
or is it possible to do a search with % before or after the text to search.

It already is verry good but when this option is possible its even better.

**DanExel** · 12-12-2010, 04:15 PM

Here is your fix for your error.
will look into your new queries.
The problem was in "Named ranges"

**DanExel** · 12-13-2010, 08:36 AM

You will have to use the instr funktion.
if you need help, shout
Regards
Dan

Thread: search a string and copy rows thru vba

Thread Tools

Display

search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Re: search a string and copy rows thru vba

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions