fixing error search data into textbox on userform

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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)
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
hi, dante
i changed as you said now the error is gone but no show the data in the rests textbox2,3,4 !
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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.
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

this is my data

12.JPG


11.JPG
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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)
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010

ADVERTISEMENT

it doesn't success
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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
 

abdelfattah

Well-known Member
Joined
May 3, 2019
Messages
963
Office Version
  1. 2010
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,262
Messages
5,595,154
Members
413,971
Latest member
User786

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