Multiple Instances In Search

[Faiek]

Hi,
I have this code used to search:

Dim Crossrefvalue As Variant
Crossrefvalue = Application.VLookup(Search.TextBox1.Value, Range("Crossreftable"), 2, 0)

Is there a way to make a code that uses this search and see's if there are multiple instances of the searched value, and if there is display both values and there offset-ted values in other textboxes.

Thanks

 

[Jerry Sullivan]

Hi Faiek,

The Find...FindNext solution to your recent post seems like a much better approach than using a Vlookup.

http://www.mrexcel.com/forum/showthread.php?t=558175&page=2

Vlookup is usually only used when you need to find a single record and it isn't very good for multiple instances.

Is there some reason the Find...FindNext approach isn't doing what you need it to do?

 

[Faiek]

Hi Faiek,

The Find...FindNext solution to your recent post seems like a much better approach than using a Vlookup.

http://www.mrexcel.com/forum/showthread.php?t=558175&page=2

Vlookup is usually only used when you need to find a single record and it isn't very good for multiple instances.

Is there some reason the Find...FindNext approach isn't doing what you need it to do?

Thanks, Your right, One Question thought How Do I Only Use the Find Function to search in a specific column and not the whole sheet?

 

[Jerry Sullivan]

Thanks, Your right, One Question thought How Do I Only Use the Find Function to search in a specific column and not the whole sheet?

If you only want to search Column B, at these two locations in your code.....

Set FoundCell = wks.Cells.Find(what:=Me.TextBox1.Value, _
    LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
'....
'....
Set FoundCell = wks.Cells.FindNext(After:=FoundCell)

...replace .Cells with .Columns("B")

Set FoundCell = wks.Columns("B").Find(what:=Me.TextBox1.Value, _
    LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
'....
'....
Set FoundCell = wks.Columns("B").FindNext(After:=FoundCell)