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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,120
Messages
5,857,500
Members
431,882
Latest member
saaaaaaaaaaaaaaaaaaaaaa

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top