?
Solved

Need a macro to search and results in excel.

Posted on 2007-07-30
22
Medium Priority
?
299 Views
Last Modified: 2010-03-05
Hi,

I have uploaded a excel fine.I have all data in Sheet 1.I have the search fields in Sheet 2.What i want is when i enter any data in Sheet 2 colum 2.It has to copy the full lines found to a new sheet.
EX:
Say
I put 512 in the Ram box.It has to search the sheet 1 and copy all Rams with 512.
http://www.filepatio.com/3628

regards
Sharath
0
Comment
Question by:bsharath
  • 11
  • 11
22 Comments
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19598230
'Paste this code in sheet2 module
Private Sub Worksheet_Change(ByVal Target As Range)
static s2row
   if target.column=2 then
        For i=1 to Sheet1.UsedRange.Rows.Count
          For i=1 to Sheet1.UsedRange.Columns.Count
              if Sheet1.Cells(i,j).value = target.value then
                 Sheet1.Rows(i).Copy Destination:=Sheet2.Rows(s2row+1)
                 s2row=s2row+1
              End if  
          Next
        Next

End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19598928
did you check the file i attached.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19598957
it is in Excel 2007 format
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 11

Author Comment

by:bsharath
ID: 19598974
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599054
'Check this works fine
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
If Target <> "" Then
   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If Sheet1.Cells(i, j).Value = Target.Value Then
                 Sheet1.Rows(i).Copy Destination:=Sheet3.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub



0
 
LVL 11

Author Comment

by:bsharath
ID: 19599071
I get this.

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

Expected: identifier
---------------------------
OK   Help  
---------------------------
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599104
did u paste this in the sheet2 module?
where r u getting the error in which line
0
 
LVL 11

Author Comment

by:bsharath
ID: 19599217


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

Only comments may appear after End Sub, End Function, or End Property
---------------------------
OK   Help  
---------------------------


0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599348
just delete everytinh in the module and paste this again
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
If Target <> "" Then
   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If Sheet1.Cells(i, j).Value = Target.Value Then
                 Sheet1.Rows(i).Copy Destination:=Sheet3.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19599428
Can you give the macro name too.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19599463
'you cannot name this macro because its an event
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
If Target <> "" Then
   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If Sheet1.Cells(i, j).Value = Target.Value Then
                 Sheet1.Rows(i).Copy Destination:=Sheet3.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub


0
 
LVL 11

Author Comment

by:bsharath
ID: 19600175
When i run this it asks me a macro name to run
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19602494
dont run thiss buddy, just type something in the Sheet2 , e.g 2 GB in RAM and see the results in sheet3
0
 
LVL 11

Author Comment

by:bsharath
ID: 19606253
Tried this does not work
I should just paste your code in the sheet 2 and type the data you said am i correct?
I did the same but no results.
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19606366
>I should just paste your code in the sheet 2
no dont paste the code in sheet2,
press Alt + F11 to open the VB Editor , double click Sheet2 on the left pane and on the right paste my code.
then type in sheet2
0
 
LVL 11

Author Comment

by:bsharath
ID: 19606414
Ok got it.Is all the headers linked.When i try for ram,HDD,FDD its working in the machine name when i type it does not how
0
 
LVL 11

Author Comment

by:bsharath
ID: 19606435
Once i put CDD in the box and i change to DVD the results are not copied.Can you crete new sheets for each search
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19606473
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
s2row = 1
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), , xlWorksheet)
If Target <> "" Then
   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If Sheet1.Cells(i, j).Value = Target.Value Then
                 Sheet1.Rows(i).Copy Destination:=sh.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub




0
 
LVL 11

Author Comment

by:bsharath
ID: 19606500
Ok great.This is creating a new sheet even when i delete a content in the excel
Can you change the case sensivity
0
 
LVL 13

Expert Comment

by:hiteshgoldeneye
ID: 19606516
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
s2row = 1
Dim sh As Worksheet
Set sh = ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), , xlWorksheet)
If Target <> "" Then
   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If UCase(Sheet1.Cells(i, j).Value) = UCase(Target.Value) Then
                 Sheet1.Rows(i).Copy Destination:=sh.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub

0
 
LVL 11

Author Comment

by:bsharath
ID: 19606555
What about the deleting
After i enter fdd and then delete the content.It creates a blank sheet.
0
 
LVL 13

Accepted Solution

by:
hiteshgoldeneye earned 2000 total points
ID: 19606641
ok solved
Private Sub Worksheet_Change(ByVal Target As Range)
Static s2row
s2row = 1
Dim sh As Worksheet
If Target <> "" Then
Set sh = ActiveWorkbook.Sheets.Add(, ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count), , xlWorksheet)

   If Target.Column = 2 Then
        For i = 1 To Sheet1.UsedRange.Rows.Count
          For j = 1 To Sheet1.UsedRange.Columns.Count
              If UCase(Sheet1.Cells(i, j).Value) = UCase(Target.Value) Then
                 Sheet1.Rows(i).Copy Destination:=sh.Rows(s2row + 1)
                 s2row = s2row + 1
              End If
          Next
        Next
    End If
End If

End Sub
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
Windows 8 came with a dramatically different user interface known as Metro. Notably missing from that interface was a Start button and Start Menu. Microsoft responded to negative user feedback of the Metro interface, bringing back the Start button 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…

850 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