Problem filling textBox

[Andre]

Hi everybody,

I've got a problem that i'cant fix. Sure need some help.

On a form i've got a certain amount of textboxes. When I fill in a number in textbox1 and the number is allready in column A, I want it to fill textbox2 with the data that is in column B (same row). The procedure starts when i exit textbox1.

The code i've got sofar is this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

Sheets("Data1").Select
Range("A2").Select
Selection.End(xlDown).Select

Do Until ActiveCell.Value = "number" '(is the header of column A)

    If ActiveCell.Value = TextBox1.Value Then
    TextBox2.Value = ActiveCell.Offset(0, 1).Value
    Else
    ActiveCell.Offset(-1, 0).Select
    End If

Loop

End Sub

With this code nothing is put in textbox2 while the value i put in textbox1 is certainly in column A.


As you can see the loop works from the bottom of the column to the top. This is because the newest data is in the last cell of the range.

I hope anybody can help me out.

Greetings Andre

 

[acw]

Andre

Try

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 TextBox2.Value = ""
 Set findit = Sheets("data1").Range("a:a").Find(what:=Val(TextBox1.Value))
 If Not findit Is Nothing Then TextBox2.Value = findit.Offset(0, 1).Value

End Sub

Tony

 

[Andre]

Thanx Tony,

Smart programming.

Your code works perfectly.

Greetings Andre