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
--
Regards,
Tom Ogilvy
"Stuart" wrote:
> 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
|
Similar Threads
