VLookup from TextBox to TextBox

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
Good Day
I hava a question regarding vlookup and Textbox's

Legend:
Sample Data Range:"A3 to B6"
in Userform
TextBox1 and TextBox2
Command Button

When I Enter a Value in TextBox1 = column A3 in worksheet,And Press the Command Button

Result shows in TextBox2= column B3 in worksheet.

VBA Code:
Private Sub CommandButton1_Click()
    Dim z As Double
    z = TextBox1.Value
    TextBox2.Text = Application.WorksheetFunction.VLookup(z, Sheets("Sheet1").Range("A3:B6"), 2, False)
End Sub

My issues are;
1.Instead of Click Event can i use Change Event instead(remove command button and key directly to TextBox1)
2.Everytime I make a Name Range for my table("A3:B6") the code stop working.
 

Attachments

  • testuserform.jpg
    testuserform.jpg
    118.7 KB · Views: 23
  • testdata.jpg
    testdata.jpg
    129.4 KB · Views: 24
You can use DropBox w=or whatever free there but in this case probably just capture the sheet (region of interest perhaps)

It is adviseable in future to use Xl2BB (the icon on the right most) to install Add-In that enable helpers out there to get actual sheet.
yeah i was trying to dl xl2bb but for some reason i cant find it in the add ins.Can i send it to your email instead?
 
Upvote 0

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
If you can show your working sheet, perhaps we can find something?
Critical Stock V3.1.xlsm
ABCDEFGHIJKLMNOPQRST
1IDPROD IDICU PRODUCTPREVIOUS STATUSARTG IDARTG nameShortage statusSupply impact start dateSupply impact end dateUsage End DateAvailabilityReasonManagement actionLast updatedOn BackOrder (Y/N)On Contract (Y/N)ActionAlternative BrandStatusRemarks
2222839yesto see98648AERON 500 ipratropium bromide (as monohydrate) 500 microgram/mL inhalation ampouleAnticipated1/01/202230/06/202226/10/2021AvailableOther13/05/2021NNoAlternate brandAPO, AtroventPending
3338874nonothing213496APO-DONEPEZIL donepezil hydrochloride (as monohydrate) 10 mg tablet blister packAnticipated8/10/202111/02/202224/10/2021AvailableManufacturing13/08/2021NNoAlternate brandSandoz, AriceptPending
4444701noantic62099AUSGEM gemfibrozil 600mg tablet bottleAnticipated10/12/202125/02/202226/02/2022AvailableUnexpected increase in consumer demand16/09/2021NNoETA 21/09/21
5545411asasano241714Bivalirudin APOTEX bivalirudin (as trifluoroacetate) 250mg powder for injection vialAnticipated10/09/202119/11/202119/09/2021AvailableManufacturing7/09/2021NYesAlternate agentPending2 months
66225sdsdfdfdfdf92418CERNEVIT multivitamin powder for injection vialAnticipated14/06/202130/09/202110/05/2021AvailableManufacturing21/05/2021NNoNo Action Required
77473sdsdssdsds50511CLOZARIL clozapine 100mg tablet blister packAnticipated10/12/202131/12/202124/10/2021AvailableManufacturingsdsds3/05/2021NYesSAS?Pending4 months
88517sdsssdsdsds50510CLOZARIL clozapine 25mg tablet blister packAnticipated12/11/202131/12/202122/11/2021AvailableManufacturing3/05/2021NYesSAS?Pending4 months
99asasasasasyesanticpatied2555sec solano testResolved2506202125072021asassaAvailableqasasasasnothng11102021YesYesasasasasasaActionedasaasa
10
Data
 

Attachments

  • error2.jpg
    error2.jpg
    178.7 KB · Views: 13
Upvote 0
Looks to me your Sheet name is Data, not shData.
 
Upvote 0
Just like in Excel, the lookup value has to be the leftmost column, but your table starts from column A. It should be column B where the value you look for resides. The table should be from row 2:
B2:T9

On the sheet name, I believe if you want to use sheet index, it should be
shData.Range("B2:T9")

if you want to use sheet name then
Sheets("Data").Range("B2:T9")
 
Upvote 0
Just like in Excel, the lookup value has to be the leftmost column, but your table starts from column A. It should be column B where the value you look for resides. The table should be from row 2:
B2:T9

On the sheet name, I believe if you want to use sheet index, it should be
shData.Range("B2:T9")

if you want to use sheet name then
Sheets("Data").Range("B2:T9")
haha yep it worked,when i used the value from column 2 and started with "B2:T9"
I just have to move around the location of the values.
Btw how do i make my Range as a Dynamic range using "=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))?
 
Upvote 0
haha yep it worked,when i used the value from column 2 and started with "B2:T9"
I just have to move around the location of the values.
Btw how do i make my Range as a Dynamic range using "=OFFSET(origin,0,0,COUNTA(range),COUNTA(range))?
I think that has to different topic, else difficult for those who is trying to search for solution based on Title
 
Upvote 0
I think that has to different topic, else difficult for those who is trying to search for solution based on Title
ahh noted.Thank you so much for all the help.Bless you man.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

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