Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use number in text box to look up value in a workbook sheet
Hi all,
I would like to use the number that it typed into the textbox "txt_PrtNumber" and then do a Vlookup in the sheet "Local Parts" to find the part description in column 2 of that sheet. Could i have some help please, i have used some code from Dave Pieterson But keep getting an error. Any help would be much appreciated... myPartNum = Me.txt_PrtNumber.Value If vmyComma = 0 Then Set LookupRange = Workbooks("Logistics_Cost_TM_Input.xls").Worksheet s("Local Parts").Range("A2:B65536") If myPartNum < "" Then res = Application.VLookup(myPartNum, LookupRange, 2, 0) If IsError(res) Then res = Application.VLookup(CDbl(myPartNum), _ Workbooks("Logistics_Cost_TM_Input.xls").Worksheet s("Local Parts").Range(LookupRange), 2, False) Me.Chk_NoPrtNo.Enabled = False Me.txt_PrtDescription.Value = "The part Derscription will be inserted in the request" Me.txt_PrtDescription.Enabled = False Else MsgBox "Description not found, please enter", vbOKOnly + vbCritical, "ERROR - Description not found" Me.txt_PrtDescription.Enabled = True Me.txt_PrtDescription.Text = vbNullString Me.Chk_NoPrtNo.Enabled = False Exit Sub End If End If Else Me.Chk_NoPrtNo.Enabled = False Me.txt_PrtDescription.Value = "The part Derscriptions will be inserted in the request" Me.txt_PrtDescription.Enabled = False End If -- Les |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Use number in text box to look up value in a workbook sheet
Hi
What error? What line is the error? Are any of your variables declared - maybe that creates an error? Part of your code is anticipating an error - is that your error...? And so on. We can't see what you can see, so you have to work pretty hard to describe your problem. As a starter, you could try err.clear on error resume next at the top of your code. VLookup will generate an error if it can't find what you are looking for. If this doesn't work, remove it or it will suppress your real error! regards Paul On Sep 17, 2:03*pm, Les wrote: Hi all, I would like to use the number that it typed into the textbox "txt_PrtNumber" and then do a Vlookup in the sheet "Local Parts" to find the part description in column 2 of that sheet. Could i have some help please, i have used some code from Dave Pieterson But keep getting an error. Any help would be much appreciated... * * myPartNum = Me.txt_PrtNumber.Value * * If vmyComma = 0 Then * * * * Set LookupRange = Workbooks("Logistics_Cost_TM_Input.xls").Worksheet s("Local Parts").Range("A2:B65536") * * * * If myPartNum < "" Then * * * * * * res = Application.VLookup(myPartNum, LookupRange, 2, 0) * * * * * * If IsError(res) Then * * * * * * * * res = Application.VLookup(CDbl(myPartNum), _ Workbooks("Logistics_Cost_TM_Input.xls").Worksheet s("Local Parts").Range(LookupRange), 2, False) * * * * * * * * Me.Chk_NoPrtNo.Enabled = False * * * * * * * * Me.txt_PrtDescription.Value = "The part Derscription will be inserted in the request" * * * * * * * * Me.txt_PrtDescription.Enabled = False * * * * * * Else * * * * * * * * MsgBox "Description not found, please enter", vbOKOnly + vbCritical, "ERROR - Description not found" * * * * * * * * Me.txt_PrtDescription.Enabled = True * * * * * * * * Me.txt_PrtDescription.Text = vbNullString * * * * * * * * Me.Chk_NoPrtNo.Enabled = False * * * * * * * * Exit Sub * * * * * * End If * * * * End If * * Else * * * * Me.Chk_NoPrtNo.Enabled = False * * * * Me.txt_PrtDescription.Value = "The part Derscriptions will be inserted in the request" * * * * Me.txt_PrtDescription.Enabled = False * * End If -- Les |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can an Excel workbook number sheet pages sequentially? | Excel Worksheet Functions | |||
a number is replaced by text when inserted anywhere in the sheet | Excel Programming | |||
a number is replaced by text when inserted anywhere in the sheet | Excel Programming | |||
a number is replaced by text when inserted anywhere in the sheet | Excel Worksheet Functions | |||
Find all text instances in a sheet and add one number from each row | Excel Discussion (Misc queries) |