Search

[DarReNz]

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]

Try putting this in the code module for the worksheet test

Then change the value in B23 and hit return

Gibbo

[VBA] 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 [/VBA]

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]

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


[VBA]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
[/VBA]

[gibbo1715]

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]

I had a feeling someone would show a better way

Not a better way, just a couple of techniques

[gibbo1715]

Im always learning and keen to do so

Thanks Again

Gibbo

[DarReNz]

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]

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

[VBA] 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

[/VBA]

[DarReNz]

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