Range question

[Paul15]

I have the below code in a workbook which works correctly for that workbook. It picks up the entered 3 letter country code from column E, entered by a user in a user form, and then automatically selects the corresponding full nationality from column F.

IE user selects/enters GBR in a combo box on the form, (this information is in my formulas tab column E of the workbook) and the code will automatically select the corresponding full nationality (this in column F of the formulas tab)

GBR	United Kingdom

I now wish to reuse this code on a different workbook where the column details are

123

Smith

The user will select/enter 123, or corresponding staff No and the code will automatically select the staff member by name.

Staff No is combo box Warr and Name is text box Staf1

any help please

Private Sub docdetails_AfterUpdate()

    Dim myRange As Range, f As Range

    Set myRange = Worksheets("Formulas").Range("E:F")

    Set f = myRange.Find(What:=Warr.Value, LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)
    If f Is Nothing Then
        Staf1.Value = ""
    Else
        Staf1.Value = f.Offset(, 1)
    End If


End Sub

 

[Fluff]

Are the staff codes all numbers?

 

[Paul15]

Are the staff codes all numbers?

Hi, yes staff codes are numbers up to 5 digits. My guess I should be looking at integers but only a guess

 

[Fluff]

In that case try

Set f = myRange.Find(What:=Val(Warr.Value), LookIn:=xlValues, Lookat:=xlWhole, MatchCase:=False)