Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form to enter data into excel | New Users to Excel | |||
Creating an Form to enter data | Excel Discussion (Misc queries) | |||
Creating a form to enter data | New Users to Excel | |||
Create a form in excel so I can enter data using DataForm | Excel Discussion (Misc queries) | |||
Can I use a FORM to enter data if I have more than the 32 fields? | Excel Discussion (Misc queries) |