Home » Forums » Open Source Learning Center » Question and Answer

Recommendations on best method to copy specific cells in a Form to uniquely ID'd row in a different worksheet

Submitted by DougF on 3 December, 2010 - 19:23

Hi there, I have a unique (to me problem).  I want to copy specific cells in a form that belongs to one unique ID into a second worksheet.  It will overwrite the cell values in the second worksheet.  I have a unique ID associated with both worksheets and the second worksheet is sorted by a location and then the ID.  The location is a number like 101 and a six digit ID. 

What are your recommendations for this?  Vlookup on a named range of the ID and then hard code the offset and update the cell values? 

I have a mixed bag of 2003 and 2007 and 2010 users.   Ideas welcome and if anyone an approx code sample that would be great...keep me from grinding it out.

 

Thank you in advance (very much)!

 

Regards,

Doug

 

P.S., I have  uploaded images.  Form is the form, I identified the relevant cells to copy based on the staff ID.  Image 2 has the row by row data; I need to look up the ID, and using macro or formula update the row data.

AttachmentSize
Eval Form.jpg146.8 KB
Eval Form2 .jpg241.08 KB
‹ Using VBA to insert or delete row and column automatic display copied text by a msgbox in VB ›
»

post example sheet

Submitted by Anonymous on 5 December, 2010 - 21:10.

hi Doug
pls add a simple example sheet to your post

= I have this data, and I want this output.

Thanks

»

Thanks Nick, I am doing that now

Submitted by Anonymous on 6 December, 2010 - 16:27.

Up loading sample 2007 spreadsheet with form and 2nd tab.

»

Data entry system

Submitted by Anonymous on 6 December, 2010 - 17:53.

hi Doug

It looks like you're creating a data entry system

this can get pretty involved, but I'll start you off.

if the name of the person whose information you are amending (let's say their age) is in cell A1, their age is in cell A2, the column the person is in in the database sheet is column 1, and their age is in col 2.

This code will locate the person's name, and update their value: 

set FoundPerson = sheets("Databse").find (Range("A1").value)
if not FoundPerson is nothing
' you found the person
sheets("Database").cells(FoundPerson.row,2).value = Range("A1").value
else
' you didn't find them
msgbox "couldn't find the person, add them"
endif
Hope that helps. Nick
»