Search tool in userform

[cken21]

Hi,

I am trying to create a search tool on a user form, i need it to search an excel sheet using one field (Text 4 characters) and have it return the data from the two adjacent cells after i click a command button. e.g

Search field:

Initials: CKKS

Returns

Manager: 1
Personnel: 123456

I am a novice with Visual basic any help would be greatly appreciated

 

[SkipVought]

hi,

What have you tried? Have you tried using the FIND feature? Have you tried using your macro recorder?

Please post the code that you are having trouble with.

Please post any error messages and the statement in error.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Thanks for replying,

I honestly don't know where to start with this, i created the userform to record the data and now have been asked to input this search tool. Do you have any tips which could get me started?

 

[SkipVought]

In Excel is the FIND feature.

Turn on your macro recorder and record FINDING something.

Post back with your recorded code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Sub Find()
'
' Find Macro
' Macro recorded 19/10/2011 by Chris Kennedy
'

'
Cells.Find(What:="PBKS", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
End Sub

 

[SkipVought]

good!

Now what is the name of the sheet on which you are you searching?



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

ConsultantData

 

[SkipVought]

Paste this into a module and call it in your form.

SearchVal is the search value
Field is the field value that you want returned: either personnel or manager or any other field on this sheet.

Function FindIt(SearchVal As String, Field As String) As String
'
' Find Macro
' Macro recorded 19/10/2011 by Chris Kennedy
'

'
    Dim rFound As Range
    
    With Sheets("ConsultantData")
        Set rFound = Cells.Find( _
            What:=SearchVal, _
            After:=.Cells(1, 1), _
            LookIn:=xlFormulas, _
            LookAt:=xlPart, _
            SearchOrder:=xlByRows, _
            SearchDirection:=xlNext, _
            MatchCase:=False, _
            SearchFormat:=False)
        If Not rFound Is Nothing Then
            FindIt = .Cells(rFound.Row, Cells.Find(Field).Column)
        Else
            FindIt = "NOTHING"
        End If
    End With
End Function

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Hi,

I have edited the code but i am getting a run time error, can you tell me what i have keyed incorrectly


Function FindIt(tbInitials As String, tbTeamS As String, tbPerS As String) As String
'
' Find Macro
' Macro recorded 19/10/2011 by Chris Kennedy
'

'
Dim rFound As Range

With Sheets("ConsultantData")
Set rFound = Cells.Find( _
What:=tbInitials, _
After:=.Cells(1, 1), _
LookIn:=xlFormulas, _
LookAt:=xlPart, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False, _
SearchFormat:=False)
If Not rFound Is Nothing Then
FindIt = .Cells(rFound.Row, Cells.Find(B).Column)
FindIt = .Cells(rFound.Row, Cells.Find(c).Column)
Else
FindIt = "NOTHING"
End If
End With
End Function

 

[SkipVought]

Why did you change it?

What was your purpose?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Sorry, I misunderstood.

Basically i have 3 text boxes, only one should be completed by the user which is 'Initials'

When the command button is pressed i was hoping the code would return Manager name in tbTeam & Personnel Number is tbPersS

 

[SkipVought]

Functions are designed to return a value, just like a spreadsheet function that you might use on a sheet.

To return those values, you would ...

tbTeam.Text = FindIt("CKKS","manager")
tbPersS.Text = FindIt("CKKS","personnel")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Ok, i am trying your original code now and am getting runtime error 424 object required

 

[SkipVought]

error on WHAT STATEMENT???

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Private Sub cmdSearch_Click()
Call FindIt
End Sub

 

[SkipVought]

1) FindIt returns a value as illustrated in my post of 19 Oct 11 9:24

2) FindIt has TWO required arguments, that you have not supplied!!!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Here's what it might be, if I got all your controls correct...

Private Sub cmdSearch_Click()
    tbTeam.Text = FindIt(tbInitials.Text, "manager")
    tbPersS.Text = FindIt(tbInitials.Text, "personnel")
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Im just not getting this, it's clearly not your fault as you have explained this as simply as possible. Thanks for your help

 

[SkipVought]

On your ConsultantData sheet, do you have column headings that include...
[tt]
Manager

Personnel
[/tt]
If not, then you should and must.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[cken21]

Yes,

The last piece of code you posted has worked, however it only returns the value for Personnel and not Manager