fixing error search data into textbox on userform

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
1,429
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
hello
i have data in sheet1 begins from d2:g and userform contains 4 textbox (textbox1=col(d),textbox2=col(e) ,textbox3=col(f),textbox4=col(g) and when i write data in textbox1 based on column d it gives me error "run time error 1004"
5.JPG
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
Because if you start looking in column D, column D is 1, column E is 2, and so on.

Then:
VBA Code:
    TextBox2.Value = WorksheetFunction.VLookup(Val(TextBox1.Value), Range("D:G"), 2, 0)
    TextBox3.Value = WorksheetFunction.VLookup(Val(TextBox1.Value), Range("D:G"), 3, 0)
    TextBox4.Value = WorksheetFunction.VLookup(Val(TextBox1.Value), Range("D:G"), 4, 0)
 
Upvote 0
hi, dante
i changed as you said now the error is gone but no show the data in the rests textbox2,3,4 !
 
Upvote 0
What data do you put in textbox1?
What data do you have in columns D, E, F and G?
You can put a sample of your data range, use XL2BB tool.
 
Upvote 0
If you have texts you should not use the Val () function this function converts any text to 0, so it cannot find it.
try this:

VBA Code:
   TextBox2.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 2, 0)
   TextBox3.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 3, 0)
   TextBox4.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 4, 0)
 
Upvote 0
What data do you put in textbox1?
You did not respond to that.

You should also remove it in the count statement

Rich (BB code):
Private Sub TextBox1_Change()
  Dim lr As Variant
  lr = WorksheetFunction.CountIf(Range("D:D"), TextBox1.Value)
  If TextBox1.Value <> "" And lr = 1 Then
    TextBox2.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 2, 0)
    TextBox3.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 3, 0)
    TextBox4.Value = WorksheetFunction.VLookup(TextBox1.Value, Range("D:G"), 4, 0)
  Else
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
  End If
End Sub
 
Upvote 0
This is another way

VBA Code:
Private Sub TextBox1_Change()
  Dim f As Range
  TextBox2.Value = ""
  TextBox3.Value = ""
  TextBox4.Value = ""
  Set f = Range("D:D").Find(TextBox1.Value, , xlValues, xlWhole)
  If Not f Is Nothing Then
    TextBox2.Value = f.Offset(, 1)
    TextBox3.Value = f.Offset(, 2)
    TextBox4.Value = f.Offset(, 3)
  End If
End Sub
 
Upvote 0
Solution
first of all about your question about textbox1 what i write in texbox1 based on col d the second your adjusting and a new code work very well thanks for your assistance
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

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
Back
Top