Copy and move info between files
 

I need to gather information from users and have the
information stored in a excel file on a sheet with 3
columns. That information is structured like this

PERSONID - VALUE1 - VALUE2

What I would like to do with this information is have a
button on another sheet which would look into the file and
look for the corresponding ID take values 1 and 2 then
paste them into the corresponding columns for the Person.
Instead of the row been deleted this would need to be then
moved into another sheet in case this needs to be checked
afterwards.

Any Ideas?

Copy and move info between files
 

You description is not very clear, but here is a guess. It assumes a
userform with 3 textboxes. User enters Personid in Textbox1, value1 in
Textbox2, value2 in textbox3.
Sheet1 has a list of PersonId's in column A starting in row1 and you want to
place the values in Textbox2 and Textbox3 in columns B and C respectively in
the same row as the matching personid

Private Sub Commandbutton1_click()
Dim rng as Range, rng1 as Range
Dim res as Variant
Dim Personid as String
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1), .Cells(1,1).End(xldown))
End With
Personid = Textbox1.Text
res = Application.match(PersonId, rng, 0)
if not iserror(res) then
set rng1 = rng(res)
rng.offset(0,1).Value = Textbox2.Text
rng.offset(0,2).Value = Textbox3.Text
end if
End if

--
Regards,
Tom Ogilvy

"Pat Fern" wrote in message
...
I need to gather information from users and have the
information stored in a excel file on a sheet with 3
columns. That information is structured like this

PERSONID - VALUE1 - VALUE2

What I would like to do with this information is have a
button on another sheet which would look into the file and
look for the corresponding ID take values 1 and 2 then
paste them into the corresponding columns for the Person.
Instead of the row been deleted this would need to be then
moved into another sheet in case this needs to be checked
afterwards.

Any Ideas?

Copy and move info between files
 

Hi Tom,
Can you also suggest command lines within your code so that displaying the
userform is accomplished?. So that values for Textboxes 1,2, and 3 can be
entered?
TIA
Martyn



"Tom Ogilvy" wrote in message
...
You description is not very clear, but here is a guess. It assumes a
userform with 3 textboxes. User enters Personid in Textbox1, value1 in
Textbox2, value2 in textbox3.
Sheet1 has a list of PersonId's in column A starting in row1 and you want
to
place the values in Textbox2 and Textbox3 in columns B and C respectively
in
the same row as the matching personid

Private Sub Commandbutton1_click()
Dim rng as Range, rng1 as Range
Dim res as Variant
Dim Personid as String
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1), .Cells(1,1).End(xldown))
End With
Personid = Textbox1.Text
res = Application.match(PersonId, rng, 0)
if not iserror(res) then
set rng1 = rng(res)
rng.offset(0,1).Value = Textbox2.Text
rng.offset(0,2).Value = Textbox3.Text
end if
End if

--
Regards,
Tom Ogilvy

"Pat Fern" wrote in message
...
I need to gather information from users and have the
information stored in a excel file on a sheet with 3
columns. That information is structured like this

PERSONID - VALUE1 - VALUE2

What I would like to do with this information is have a
button on another sheet which would look into the file and
look for the corresponding ID take values 1 and 2 then
paste them into the corresponding columns for the Person.
Instead of the row been deleted this would need to be then
moved into another sheet in case this needs to be checked
afterwards.

Any Ideas?

Copy and move info between files
 

http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

John Walkenbach Form
http://j-walk.com/ss/dataform/index.htm


http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en...FormsPartI.asp
Part II
http://msdn.microsoft.com/library/en...ormsPartII.asp


--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Hi Tom,
Can you also suggest command lines within your code so that displaying the
userform is accomplished?. So that values for Textboxes 1,2, and 3 can be
entered?
TIA
Martyn



"Tom Ogilvy" wrote in message
...
You description is not very clear, but here is a guess. It assumes a
userform with 3 textboxes. User enters Personid in Textbox1, value1 in
Textbox2, value2 in textbox3.
Sheet1 has a list of PersonId's in column A starting in row1 and you
want
to
place the values in Textbox2 and Textbox3 in columns B and C
respectively
in
the same row as the matching personid

Private Sub Commandbutton1_click()
Dim rng as Range, rng1 as Range
Dim res as Variant
Dim Personid as String
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1), .Cells(1,1).End(xldown))
End With
Personid = Textbox1.Text
res = Application.match(PersonId, rng, 0)
if not iserror(res) then
set rng1 = rng(res)
rng.offset(0,1).Value = Textbox2.Text
rng.offset(0,2).Value = Textbox3.Text
end if
End if

--
Regards,
Tom Ogilvy

"Pat Fern" wrote in message
...
I need to gather information from users and have the
information stored in a excel file on a sheet with 3
columns. That information is structured like this

PERSONID - VALUE1 - VALUE2

What I would like to do with this information is have a
button on another sheet which would look into the file and
look for the corresponding ID take values 1 and 2 then
paste them into the corresponding columns for the Person.
Instead of the row been deleted this would need to be then
moved into another sheet in case this needs to be checked
afterwards.

Any Ideas?

Copy and move info between files
 

Thanks for the links Tom, much appreciated...
You are most helpful.
Martyn

"Tom Ogilvy" wrote in message
...
http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


http://support.microsoft.com/default...b;en-us;829070
How to use Visual Basic for Applications examples to control UserForms in
Microsoft Excel

John Walkenbach Form
http://j-walk.com/ss/dataform/index.htm


http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the
capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I

http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartI.asp
Part II

http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartII.asp


--
Regards,
Tom Ogilvy


"Martyn" wrote in message
...
Hi Tom,
Can you also suggest command lines within your code so that displaying
the
userform is accomplished?. So that values for Textboxes 1,2, and 3 can
be
entered?
TIA
Martyn



"Tom Ogilvy" wrote in message
...
You description is not very clear, but here is a guess. It assumes a
userform with 3 textboxes. User enters Personid in Textbox1, value1 in
Textbox2, value2 in textbox3.
Sheet1 has a list of PersonId's in column A starting in row1 and you
want
to
place the values in Textbox2 and Textbox3 in columns B and C
respectively
in
the same row as the matching personid

Private Sub Commandbutton1_click()
Dim rng as Range, rng1 as Range
Dim res as Variant
Dim Personid as String
With Worksheets("Sheet1")
set rng = .Range(.Cells(1,1), .Cells(1,1).End(xldown))
End With
Personid = Textbox1.Text
res = Application.match(PersonId, rng, 0)
if not iserror(res) then
set rng1 = rng(res)
rng.offset(0,1).Value = Textbox2.Text
rng.offset(0,2).Value = Textbox3.Text
end if
End if

--
Regards,
Tom Ogilvy

"Pat Fern" wrote in message
...
I need to gather information from users and have the
information stored in a excel file on a sheet with 3
columns. That information is structured like this

PERSONID - VALUE1 - VALUE2

What I would like to do with this information is have a
button on another sheet which would look into the file and
look for the corresponding ID take values 1 and 2 then
paste them into the corresponding columns for the Person.
Instead of the row been deleted this would need to be then
moved into another sheet in case this needs to be checked
afterwards.

Any Ideas?