Text Box Link
 

Hi Everyone

I have a UserForm with several Text Boxes that are filled by reading data
from "Sheet1". TBox1 has a unique part number from column "K" of "Sheet1" Is
it possible for TBox1 to read "Sheet2" find the same number in column "K" of
"Sheet2" then insert the name of the part number that is in Column "A" (on
the same row in "Sheet2") into Text Box2 on the UserForm. I can then add all
the data in the Text Boxes to "Sheet3"

--
Many thanks

hazel

Text Box Link
 

Dim rng as Range, s as Variant
Dim rng1 as Range, res as Variant
With worksheets(Sheet2")
set rng = .range(.cells(2,"K"),.cells(2,"K").end(xldown))
s = tbox1.Value
if isNumeric(s) then _
s = clng(s)
res = Application.Match(s,rng,0)
if not iserror(res) then
set rng1 = .Cells(rng(res).row,1)
tbox2.Value = rng1.value
else
tbox2.Value = "Part Not found"
end if
end With

--
Regards,
Tom Ogilvy


"Hazel" wrote:

Hi Everyone

I have a UserForm with several Text Boxes that are filled by reading data
from "Sheet1". TBox1 has a unique part number from column "K" of "Sheet1" Is
it possible for TBox1 to read "Sheet2" find the same number in column "K" of
"Sheet2" then insert the name of the part number that is in Column "A" (on
the same row in "Sheet2") into Text Box2 on the UserForm. I can then add all
the data in the Text Boxes to "Sheet3"

--
Many thanks

hazel

Text Box Link
 

Hi Tom

Thanks for the response and if I put the part number and part name to start
on row 2 it drops in to place in TB2 and transfers OK in to "Sheet3" however
we have 7 rows of information separated by a couple of blank rows, the actual
data begins on row 9 on "Sheet2". I have been trying for ages to get it to
work changing ranges and rows all I seem to get is Part Not Found except when
inserting the data on row2. Are the blank rows stopping the event from
running?? have tried deleting the rows still to no avail. I'm stumped and
tired
--
Many thanks

hazel


"Tom Ogilvy" wrote:

Dim rng as Range, s as Variant
Dim rng1 as Range, res as Variant
With worksheets(Sheet2")
set rng = .range(.cells(2,"K"),.cells(2,"K").end(xldown))
s = tbox1.Value
if isNumeric(s) then _
s = clng(s)
res = Application.Match(s,rng,0)
if not iserror(res) then
set rng1 = .Cells(rng(res).row,1)
tbox2.Value = rng1.value
else
tbox2.Value = "Part Not found"
end if
end With

--
Regards,
Tom Ogilvy


"Hazel" wrote:

Hi Everyone

I have a UserForm with several Text Boxes that are filled by reading data
from "Sheet1". TBox1 has a unique part number from column "K" of "Sheet1" Is
it possible for TBox1 to read "Sheet2" find the same number in column "K" of
"Sheet2" then insert the name of the part number that is in Column "A" (on
the same row in "Sheet2") into Text Box2 on the UserForm. I can then add all
the data in the Text Boxes to "Sheet3"

--
Many thanks

hazel

Text Box Link
 

"Hazel" wrote:

Hi Tom

Thanks for the response and if I put the part number and part name to start
on row 2 it drops in to place in TB2 and transfers OK in to "Sheet3" however
we have 7 rows of information separated by a couple of blank rows, the actual
data begins on row 9 on "Sheet2". I have been trying for ages to get it to
work changing ranges and rows all I seem to get is Part Not Found except when
inserting the data on row2. Are the blank rows stopping the event from
running?? have tried deleting the rows still to no avail. I'm stumped and
tired
--
Many thanks

hazel


"Tom Ogilvy" wrote:

Dim rng as Range, s as Variant
Dim rng1 as Range, res as Variant
With worksheets(Sheet2")
set rng = .range(.cells(2,"K"),.cells(2,"K").end(xldown))
s = tbox1.Value
if isNumeric(s) then _
s = clng(s)
res = Application.Match(s,rng,0)
if not iserror(res) then
set rng1 = .Cells(rng(res).row,1)
tbox2.Value = rng1.value
else
tbox2.Value = "Part Not found"
end if
end With

--
Regards,
Tom Ogilvy


"Hazel" wrote:

Hi Everyone

I have a UserForm with several Text Boxes that are filled by reading data
from "Sheet1". TBox1 has a unique part number from column "K" of "Sheet1" Is
it possible for TBox1 to read "Sheet2" find the same number in column "K" of
"Sheet2" then insert the name of the part number that is in Column "A" (on
the same row in "Sheet2") into Text Box2 on the UserForm. I can then add all
the data in the Text Boxes to "Sheet3"

--
Many thanks

hazel