Enter data in correct row from form

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Guest]

Line 2/3 is pretty much in the form you posted it. I have added a line
continuation character to make two separate lines that constitute one
command. Now all lines should be left as seen

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

 

[Guest]

So I'm being dumb Tom!

When I run this it fails at the line starting "If not res..." with the error
object required.

What on earth am I doing wrong?