Problem filling textBox

Andre

New Member
Joined
Sep 7, 2002
Messages
22
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:

Code:
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
 

Some videos you may like

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Andre

Try
Code:
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

New Member
Joined
Sep 7, 2002
Messages
22
Thanx Tony,

Smart programming.

Your code works perfectly.

Greetings Andre
 

Watch MrExcel Video

Forum statistics

Threads
1,118,058
Messages
5,569,957
Members
412,300
Latest member
Chaneycr
Top