Look on a table for a given "x" value and change another value on the same row

**CjTonos** · 04-06-2022, 03:44 PM

Hi guys,

First of, please forgive any writing or gramar error my thread might have. I am not a native English speaker.

I'm new to this community so I guess this is will also be my introductory post. My name is Jorge Tonos, I'm from the Dominican Republic and I work at a educational government institution at my country. I have recently found the world of endless possibilities of VBA and I'm just loving this beautiful tool, it is making my work so much easier. However, even though I am new to VBA I do have some knowledge in HTML, CSS, Jquerty, Php and just web coding in general. So, for me it isn't that hard to get how VBA syntax works as it is quite similar to web coding syntax.

With that said, let me address my problem.

The main idea of what I want to achieve is simple, I just want to be able to get the value of the cell (C4) wich is in the "Validador" sheet and I want to look up for that value in a table given at the "Personal" sheet. Afterwards, if it is found, I would like to change the data of a cell that is in the same row of the founded value with the value of another cell (C17) that is in the "Validador" sheet.

The table inside of the "Personal" sheet starts at C4 (being the 4th row the headings of the table) and it does not have an ending, it would constantly grow up throughout time. Also, I want to trigger this with a button and if there is nothing inside cell C17->"Validador" I just want the code to do nothing on the table, because otherwise it will change the table value to blank.

Notice that my entire workbook will be protected and that this "Personal" sheet will be hidden so no one but authorized personnel can access to it, don't know if I need some extra code to work with a protected workbook and a hidden sheet. The reason why it has to be like this is because there is confidential information on this "Personal" sheet.

I guess this will cover all I want to achieve. Hope someone here can help me out with this. I'll attach the workbook to this post.

Thank you all so much in advance.

**achammar** · 04-06-2022, 05:26 PM

Try this and see if it does what you want. You didn't say which value you wanted changed. You only said a value in the same row, but not which column. So this file changes the Nombre to whatever is in Cell C17 of the validator sheet. There is a button on sheet Validator. I put a comment in the code to change the Offset number that is set to 1 to however many columns over it is that you want to change. Right now it changes the first column to the right, so you can change that to whatever column you want.

**achammar** · 04-06-2022, 05:38 PM

And welcome to the forum!!

**CjTonos** · 04-06-2022, 07:20 PM

Originally Posted by achammar

Try this and see if it does what you want. You didn't say which value you wanted changed. You only said a value in the same row, but not which column. So this file changes the Nombre to whatever is in Cell C17 of the validator sheet. There is a button on sheet Validator. I put a comment in the code to change the Offset number that is set to 1 to however many columns over it is that you want to change. Right now it changes the first column to the right, so you can change that to whatever column you want.

Thank you for the welcome bro and more than anything for taking the time to help me, I truly appreciate that man. I just saw the code, did what you suggested on the comment, and what you did is just perfect, exactly what I wanted to do, again, thank you. When I get to work tomorrow I'll study the way you got there, will do some research on every parameter or function you've used to see what's going on. My bad for missing the column that I wanted to change on the table (G), I tried to be very specific, and then I end up missing the most important thing... -.-" nice ahahahah.

**achammar** · 04-06-2022, 08:41 PM

Good, I'm glad it worked! And you are very welcome!

**achammar** · 04-06-2022, 08:56 PM

Also, I changed it a little bit. I removed 2 lines of code that I realized were not necessary. Also, there are 2 lines in the code where you have to change the 1 in the Offset command to which column you want changed, not just one line like I said before. They are both commented. Here is the new file.
If you have any questions don't hesitate to ask!

**CjTonos** · 04-12-2022, 10:20 AM

Originally Posted by achammar

Also, I changed it a little bit. I removed 2 lines of code that I realized were not necessary. Also, there are 2 lines in the code where you have to change the 1 in the Offset command to which column you want changed, not just one line like I said before. They are both commented. Here is the new file.
If you have any questions don't hesitate to ask!

I forgot to reply to you bro, I'm sorry for that. I did what you suggested and also added a few lines of code so I can deal with the protected book. It's all working like a charm. Again, thank you so much for your help.

**achammar** · 04-12-2022, 10:25 AM

Good, I'm glad it's working for you!

If you are happy with the help I gave you I hope you could take a short moment and click on the "Add reputation" star in one of my posts and help me move up. I was happy to help and I hope it continues to work good for you!

Look on a table for a given "x" value and change another value on the same row

LinkBack

Thread Tools

Rate This Thread

Display

Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Re: Look on a table for a given "x" value and change another value on the same row

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

[SOLVED] Need assistance with "Pivot Table", "Slicer", and "Dynamic/Interactive Graph"

how to change default name in pivot table "count of" and permanently display "zero"

[SOLVED] How to USE """"" cells count """"" change font color

Pivot Table - How to "add a data column" "of the same field" "across multiple sheets"

[SOLVED] Is there a way to change the error "div/0" in data table to "--" using cond. formatting?

[SOLVED] How to Count number of "Error" and "OK" after the word "Instrument" found in table row

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions