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

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