Hi all,

I have a sheet "Local Parts" of part numbers in column "A" and the part
description in column "B".
I have a user form "frm_InputTM" with a text box "txt_PrtNum" where the user
will enter the part number, i then want to look up this number in the "Local
Parts" sheet and return the description and insert it in the text box
"txt_PrtDescription"

Could anybody please help me with this... thanks in advance...

--
Les

I have this code, but it is not working ??

Txt_PrtDescription.Value = Excel.WorksheetFunction.VLookup(txt_PrtNum.Value,
Sheets("Local Parts").Range("A2:B65536"), 2, False)

Gratefull for any help....


--
Les


"Les" wrote:

Hi all,

I have a sheet "Local Parts" of part numbers in column "A" and the part
description in column "B".
I have a user form "frm_InputTM" with a text box "txt_PrtNum" where the user
will enter the part number, i then want to look up this number in the "Local
Parts" sheet and return the description and insert it in the text box
"txt_PrtDescription"

Could anybody please help me with this... thanks in advance...

--
Les

Why not read the part numbers and part descriptions into a two
dimensional array in the Userform_Initialize Event.

Dim arrData() As Variant
Dim ColACount As Long
Dim i As Long

ColACount = Range(Range("A1"), Range("A" &
Rows.Count).End(xlUp)).Count

ReDim arrData(1 To ColACount, 1 To 2)

For i = 1 To ColACount
arrData(i, 1) = Range("A" & i).Value
arrData(i, 2) = Range("B" & i).Value
Next i

Now you can look up the part number from txt_PrtNum in the array, and
wherever you find it, you know that the next column over is the data
you want.

For example if the part number was 12345 and it was stored in
arrData(10,1), then you simply return arrData(10,2) (assuming you did
set up your data table correctly)

HTH,
JP


On Sep 4, 10:44*pm, Les wrote:
Hi all,

I have a sheet "Local Parts" of part numbers in column "A" and the part
description in column "B".
I have a user form "frm_InputTM" with a text box "txt_PrtNum" where the user
will enter the part number, i then want to look up this number in the "Local
Parts" sheet and return the description and insert it in the text box
"txt_PrtDescription"

Could anybody please help me with this... thanks in advance...

--
Les

Thanks JP, much appreciated...
--
Les


"JP" wrote:

Why not read the part numbers and part descriptions into a two
dimensional array in the Userform_Initialize Event.

Dim arrData() As Variant
Dim ColACount As Long
Dim i As Long

ColACount = Range(Range("A1"), Range("A" &
Rows.Count).End(xlUp)).Count

ReDim arrData(1 To ColACount, 1 To 2)

For i = 1 To ColACount
arrData(i, 1) = Range("A" & i).Value
arrData(i, 2) = Range("B" & i).Value
Next i

Now you can look up the part number from txt_PrtNum in the array, and
wherever you find it, you know that the next column over is the data
you want.

For example if the part number was 12345 and it was stored in
arrData(10,1), then you simply return arrData(10,2) (assuming you did
set up your data table correctly)

HTH,
JP


On Sep 4, 10:44 pm, Les wrote:
Hi all,

I have a sheet "Local Parts" of part numbers in column "A" and the part
description in column "B".
I have a user form "frm_InputTM" with a text box "txt_PrtNum" where the user
will enter the part number, i then want to look up this number in the "Local
Parts" sheet and return the description and insert it in the text box
"txt_PrtDescription"

Could anybody please help me with this... thanks in advance...

--
Les