How to replace values in a table based on a cell using VBA

I'm having a really hard time finding a light (computacional wise) way to find a value in a cell and replacing all values on the row this value appear. I tried to use the match function, but to no avail, as I'm a begginer in VBA. I use the following code to create a new row and fill all the collumns needed (shortened, as the full code is really long):

"Private Sub UpdateButton_Click()

Dim myRow As ListRow
Dim intRows As Integer

intRows = ActiveWorkbook.Worksheets("Data").ListObjects("DataBase").ListRows.Count
Set myRow = ActiveWorkbook.Worksheets("Data").ListObjects("DataBase").ListRows.Add(intRows)


'Header filling
myRow.Range(1) = Range("C8")
myRow.Range(12) = Range("C7")
myRow.Range(13) = Range("H8")"


"Database" is a Table and Data, as the formatting suggests, a sheet. The number inside "myRow.Range" are the columns I'm adding the values to and the cell reference are cell in a form.

I'm trying to adapt this code to run the function I'm intending, but I'm not finding a way to make it work.

Here's the Sample. The first sheet we have some field that is intended to be user input, after inpunting the data, there's 2 options, the first add a new line in the table in the "Data" sheet, the second one is meant to overwrite the data, searching for the Row that have the PJ value the same entered by the user in the cell on the right side of the cell named "Search for PJ".


So, I'm trying to write a code that when the "Update Data" is clicked, to search for the value inside the cell F3 inside the first colum of the "DataBase" table and then overwrite all information with the data entered in the rows beside the button. The same code of the first button, but instead of creating a new line, it will overwrite the columns.

I must thank you for the support. But there some problems that I didn't address, the input columns of the original worksheet i'm working on isn't stacked like the sample, the input cells are all over the place, thats why I did the add method cell by cell plus some ranges. This was my mistake to not specify it.

So a thing to consider is, I can't see how to "translate" the Update code from the range B1:B11 to cell by cell basis.

PS.: I really liked the Add code, but it suffers from the same problem described above, but still, the code will help me to find new ways to do the code i'm looking for.

The structure is the same, but changed the values to reflect the last example. But both structures coexist in the same sheet, the stacked inputs and the forms that i added below.

Yes, this is exactly what I'm doing, and it's working fine when clicking on the add button, my problem is that, when I try to to the exact same funtion, but instead of creating a new line, search for the value on the F3 cell on the second sheet, and then adding the info to the same line, I can't find a way to do this efficiently (and working).

It works really well, I'll try to adapt to some needs I have, like not changing to blank, when not inputing anything on the "to update" fields.

I tried using the Match function, but the documentation was not that well written, would you have a good documentation to name?

And thank you so much for the support and patience.

About the last question, I'm trying to make a Menu base workbook to register some forms in a lighter way and way more interactive than the manual input, signing, and register of the documents I use. So I'm using the sheets as diferent pages with multiple functions, as I need to make it with Excel because of compatibility.

Originally Posted by sintek

So are you entering data on [Add and Update] sheet in the cells as per below...and then data gets added|edited to database...

Attachment 798906


Tx for rep added...This below though...really?

You gave incorrect data setup...

Attachment 798910

About this bit. I'm not a native english speaker so I might get misunderstood, what I meant was that you give me the tools to do what I wanted without giving me the final answer, allowing me to force myself to understand the principles and learn a little bit more.

Originally Posted by sintek

I suggest looking into UserForms Interface...There are hundreds of database examples on this site...

About this, I tried it at first, but we already had the form in excel format, so I spent a lot of time searching for a way to "convert" from excel format to userform, the best sollutions i found involved adding a print of the form on the background and adding the textboxes over the form, which gave a awful result, so I just decided it was better to copy and paste the info in a separate table.