Enter data in correct row from form
 

Hi guys need your expert help again.

I have created a form that takes a part number and looks this up in a range
and fills the remaining text boxes on the form. So far so good.

What I want to do is, if the user alters the data return this to the row
where I first looked up the original data.

I've searched through but can't find a way of doing it.

my code so far:
TextBox2.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 3, False)
TextBox3.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 2, False)
TextBox4.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 4, False)
TextBox5.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 5, False)
TextBox6.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 6, False)


Then button 2 would use more code to overwrite the old data with the new.

TIA

Stu

Enter data in correct row from form
 

Dim res as Variant
res = Application.Match(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 0)
if not res is nothing then
set rng = Sheets("QKIT - NET").Range("B2:B65536")(res)
rng.offset(0,2) = TextBox2.Value
rng.offset(0,1) = TextBox3.Value
rng.offset(0,3) = TextBox4.Value
rng.offset(0,4) = TextBox5.Value
rng.offset(0,5) = TextBox6.Value
End if

--
Regards,
Tom Ogilvy

"Stuart" wrote:

Hi guys need your expert help again.

I have created a form that takes a part number and looks this up in a range
and fills the remaining text boxes on the form. So far so good.

What I want to do is, if the user alters the data return this to the row
where I first looked up the original data.

I've searched through but can't find a way of doing it.

my code so far:
TextBox2.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 3, False)
TextBox3.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 2, False)
TextBox4.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 4, False)
TextBox5.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 5, False)
TextBox6.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 6, False)


Then button 2 would use more code to overwrite the old data with the new.

TIA

Stu

Enter data in correct row from form
 

Tom,
thanks for the info it's much appreciated however bearing in mind you are
helping very much a novice...

I presume that line 2 and 3 have to be joined because of the message limits
but what about line 4/5?

If I join them Vba complains 'end if without block if' and if i don't it
tells me it needs an object?

Stu

"Tom Ogilvy" wrote:

Dim res as Variant
res = Application.Match(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 0)
if not res is nothing then
set rng = Sheets("QKIT - NET").Range("B2:B65536")(res)
rng.offset(0,2) = TextBox2.Value
rng.offset(0,1) = TextBox3.Value
rng.offset(0,3) = TextBox4.Value
rng.offset(0,4) = TextBox5.Value
rng.offset(0,5) = TextBox6.Value
End if

--
Regards,
Tom Ogilvy

"Stuart" wrote:

Hi guys need your expert help again.

I have created a form that takes a part number and looks this up in a range
and fills the remaining text boxes on the form. So far so good.

What I want to do is, if the user alters the data return this to the row
where I first looked up the original data.

I've searched through but can't find a way of doing it.

my code so far:
TextBox2.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 3, False)
TextBox3.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 2, False)
TextBox4.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 4, False)
TextBox5.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 5, False)
TextBox6.Value = Application.VLookup(TextBox1.Value, Sheets("QKIT -
NET").Range("B2:G65536"), 6, False)


Then button 2 would use more code to overwrite the old data with the new.

TIA

Stu