Formula for Lookup and replace

[Cozzy123]

Hi all,

I wonder if this is possible. I have userforms used to capture data into a table, I then have a search via listboxrow to find specific rows of data to view. This all works really well and nicely.

What I want to do is edit some of the data from the search that I have found and place the edited data back into the original table. Can this be done?

either via excel sheets or VBA? As you can imagine my tables/search are excel and the userforms are used to input the data and view the data. The sheets are in the background hidden.

Cheers

 

[Yongle]

In principle ...

1. Find the data row( r ) - you say your code already does this
2. Open userform (for data edit)
3. Write data from row r into boxes on a userform

TextBox1.Value = Sheets("Data").Cells([COLOR=#ff0000]r[/COLOR] , "A") 
etc

4. Amend fields in the userform
5. Click button to write amended data back to original cells (and clear userform)

Sheets("Data").Cells([COLOR=#ff0000]r[/COLOR] , "A") = TextBox1.Value

 

[Cozzy123]

Thank you for taking the time to respond, so it sounds as it if it can be done, great!

What my code does is vlookup the text in a textbox and puts the values into other textboxs along the row via vlookup. Probably a rubbish way of doing it but it seems to work.

I'll give it a go tomorrow, and report back.

thanks again.

 

[Cozzy123]

Thanks again, I have tried this as this was my very old anc basic coding. The first part works fine:

Sheets("Table").Range("y1") = TextBoxchcrn
TextBoxchcr.Value = Application.WorksheetFunction.VLookup(Sheets("Table").Range("y1"), Sheets("Table").Range("A2:x2000"), 5, False)

Trying to edit after writing new text in the textbox, I reversed the above so:

Sheets("Table").Range("y1") = TextBoxchcrn
Application.WorksheetFunction.VLookup(Sheets("Table").Range("y1"), Sheets("Table").Range("A2:x2000"), 5, False) = TextBoxchcr.Value

but doesn't work? Obviously this doesn't work. So, how would I change my code to fit what you have above?

Regards