View Full Version : Search
DarReNz
10-24-2005, 01:29 AM
Hi,
I have 2 worksheets(Test) and (Out). Out contains many data and I want to search by Num. How do i write a search function that searches the Num ? And when input is written on B23(Test), it automatically goes to Out worksheet and perform the search and when found highlights the row ? Thanks
Eg.
Num
1 a a a
2 b b b
3 c c c
gibbo1715
10-24-2005, 02:04 AM
Try putting this in the code module for the worksheet test
Then change the value in B23 and hit return
Gibbo
Option Explicit
Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
On Error Resume Next
Set fnFind = sh.Cells.Find(What:=strFind, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchFor As Range
Dim StrNumber As String
If Target = Range("B23") Then
StrNumber = Range("B23").Value
Set SearchFor = fnFind(StrNumber, Sheets("Out"))
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
Sheets("Out").Select
SearchFor.Select
With Selection.EntireRow.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
End Sub
The above will find the first instance of the number and change the colour of the row
If you are looking to find a load of records with the same number, have a look at this kb entry http://vbaexpress.com/kb/getarticle.php?kb_id=787
It is my prefered method and is very fast, it will create a new worksheet containing just the records your interested in
Gibbo
Bob Phillips
10-24-2005, 02:30 AM
Try putting this in the code module for the worksheet test
Then change the value in B23 and hit return
Couple of points FYI.
That is not he best way to test that B23 is being changed as it will also invoke the change event in any cell with the same value as in B23. Better to test the address property.
You also do not need to select the sheet and the cell. Searchfor will address both the cell and it's sheet, so you can work directly upon that
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchFor As Range
Dim StrNumber As String
If Target.Address = "$B$23" Then
StrNumber = Range("B23").Value
Set SearchFor = fnFind(StrNumber, Sheets("Out"))
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
With SearchFor.Entirerow.Interior
.ColorIndex = 35
.Pattern = xlSolid
End With
End If
End If
End Sub
gibbo1715
10-24-2005, 02:37 AM
Couple of points FYI.
That is not he best way to test that B23 is being changed as it will also invoke the change event in any cell with the same value as in B23. Better to test the address property.
You also do not need to select the sheet and the cell. Searchfor will address both the cell and it's sheet, so you can work directly upon that
I had a feeling someone would show a better way (I thought he wanted it to select the sheet and record though Thats why i selected it)
Cheers
Gibbo
Bob Phillips
10-24-2005, 02:39 AM
I had a feeling someone would show a better way
Not a better way, just a couple of techniques http://vbaexpress.com/forum/images/smilies/001.gif
gibbo1715
10-24-2005, 02:42 AM
Im always learning and keen to do so
Thanks Again
Gibbo
DarReNz
10-24-2005, 08:30 PM
Thanks xld and gibbo, but what I meant by highlight is when you drag the cursor over the row of data that i wanted to search so that they are highlighted and positioned at that row so that I am able to know which row to edit them. How do I do that ? Thanks a million
gibbo1715
10-25-2005, 12:39 AM
sorry but im a little unsure what you mean
Is there going to be one record or more than one?
And are you saying you then want to be taken to that record so you can edit it?
If thats the case and there is only one the following slightly amended code will work for you
If not please explain exactly what you want in more detail
Cheers
Gibbo
Option Explicit
Function fnFind(strFind, Optional sh) As Range
If IsMissing(sh) Then Set sh = ActiveSheet
On Error Resume Next
Set fnFind = sh.Cells.Find(What:=strFind, _
After:=ActiveCell, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
End Function
Private Sub Worksheet_Change(ByVal Target As Range)
Dim SearchFor As Range
Dim StrNumber As String
If Target.Address = "$B$23" Then
StrNumber = Range("B23").Value
Set SearchFor = fnFind(StrNumber, Sheets("Out"))
If SearchFor Is Nothing Then
MsgBox ("Sorry Not Found")
Else
Sheets("Out").Select
SearchFor.Select
End If
End If
End Sub
DarReNz
10-25-2005, 09:41 AM
yes gibbo I wanted to be taken to the record so I can edit it as well. I have another question related to this search, if i have a column Stat that has a drop down list that contains values Pending, Waiting etc in B25(Test)
How do I search for those rows only and display in Out when those values(eg. Pending) are chosen ? Thanks
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.