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: 19
  • testdata.jpg
    testdata.jpg
    129.4 KB · Views: 19

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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: 9

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows
Looks to me your Sheet name is Data, not shData.
 

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows

Attachments

  • data error.jpg
    data error.jpg
    105.7 KB · Views: 7

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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")
 

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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))?
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
2,050
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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
 

mrsec

Board Regular
Joined
Jan 28, 2016
Messages
54
Office Version
  1. 2016
Platform
  1. Windows
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.
 
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,164,131
Messages
5,835,579
Members
430,368
Latest member
User800

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