Change value in column based on value in other column

[SHUTTEHFACE]

I'm trying to use the following formula but not getting the desired results:

Please Login or Register  to view this content.

If a value in column J is set to "Y" I'd like to the change the value in the same row but corresponding column CB to "WORK"

Thanks!

M

[stnkynts]

Please Login or Register  to view this content.

[SHUTTEHFACE]

Thanks!

Would you mind walking me through the logic?

[stnkynts]

Would you mind walking me through the logic?

When working your range of values you will need to have some way of isolating the values of each cell within the range. There are a couple of ways of doing this such as looping or filtering. I choose filtering to solve your problem. I have added comments to the code to help clarify its purpose

Please Login or Register  to view this content.

Another less efficient, but possibly more understandable, technique would be something like this

Please Login or Register  to view this content.

[SHUTTEHFACE]

Awesome thanks!

If I wanted to modify this to include values that were "N" and have the listed as "OFF" in CB would the code then be:

Please Login or Register  to view this content.

[stnkynts]

Yes that should work just fine.

[SHUTTEHFACE]

Awesome, now let's say I had to do the same thing but also including the values in column K,L,M,N,O,P which each have their respective columns EF,GJ,IN,KR,MV,OZ to change to "OFF" or "WORK". Do I just duplicate the above With statement for each one or is there a more efficient way?

[stnkynts]

Is there any reason why you can't just use formulas in the output columns? I.E. =IF(J2="Y", "WORK", ""). Then just fill down the column.

[SHUTTEHFACE]

Ah, I'm doing this because I am using them as data validation cells as well. I wanted to have the functionality of changing the individual cell to "OFF" or "WORK" as well as change it based another cell input.

[SHUTTEHFACE]

I tried the following but it doesn't work correctly.

Please Login or Register  to view this content.

[stnkynts]

Try this:

Please Login or Register  to view this content.

[SHUTTEHFACE]

Awesome, almost works flawlessly! When I change the values to Y or N, the values in the corresponding column and row are changing correctly but it is also adding "OFF" to the first row for each of those columns.

[stnkynts]

Woops. I forgot to exclude the first row. Change this line

Please Login or Register  to view this content.

to this

Please Login or Register  to view this content.

[SHUTTEHFACE]

Changing this gave me an error with this line in debug "No cells were found":

Please Login or Register  to view this content.

Could it be because all the input and outputs happen below row 7?

[SHUTTEHFACE]

I've attached a demo version of my spreadsheet so you can get a better idea of what I'm trying to do. I can't seem to get this code working properly.

Thanks for the help!demo.xlsm

[stnkynts]

It error because there were situations in which you didn't have both a "n" and "y" as a filter parameter. The below code is amended for that along with the location of the header. Keep in mind that this code should not be part of the worksheet_change event as you have it and should instead be its own stand alone subroutine. Lastly, you worksheet_change code with either vastly slow down the code or potentially cause more errors along the way:

Please Login or Register  to view this content.

[SHUTTEHFACE]

Thanks stnkynts!

What would you suggest I do in the worksheet_change code to speed up the code? I'd like to have it where is someone changes the individual day to "WORK", "OFF", "PTO", the following cells populate accordingly, either with the default schedule or blank if OFF OR PTO is set. The current code I have under workbook_change was supposed to allow me to do this.

[SHUTTEHFACE]

It error because there were situations in which you didn't have both a "n" and "y" as a filter parameter. The below code is amended for that along with the location of the header. Keep in mind that this code should not be part of the worksheet_change event as you have it and should instead be its own stand alone subroutine. Lastly, you worksheet_change code with either vastly slow down the code or potentially cause more errors along the way:

Please Login or Register  to view this content.

A couple questions:

1. How can I modify this code to change the cells to the right as well as the "WORK" & "OFF" cells. So if we set it had Y for Monday it would populate both "WORK" and the default schedule for each of the following 5 cells.

2. The values for column "P" needs to change the values for X as well as OZ. It currently only updates OZ.

Thanks!

[stnkynts]

What would you suggest I do in the worksheet_change code to speed up the code? I'd like to have it where is someone changes the individual day to "WORK", "OFF", "PTO", the following cells populate accordingly, either with the default schedule or blank if OFF OR PTO is set. The current code I have under workbook_change was supposed to allow me to do this.

Your existing worksheet_change code isn't bad or wrong. The problem occurs when you run the macro I wrote. On some circumstances it will cause the worksheet_change to trigger. You may or may not want this. I don't know what your goal is so I wasn't sure if I should have disabled the worksheet_change event from triggering in my macro (you can do this through Application.EnableEvents). If I was guessing I would do something like this (once again I am not sure what you flow is so it is up to you to understand what is going on and whether to implement this strategy or not.)

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[stnkynts]

1. How can I modify this code to change the cells to the right as well as the "WORK" & "OFF" cells. So if we set it had Y for Monday it would populate both "WORK" and the default schedule for each of the following 5 cells.

I have no idea what the default schedule is but you would do it by editing/or including another line of code for the required cell offset from rOutput

2. The values for column "P" needs to change the values for X as well as OZ. It currently only updates OZ.

Like this:

Please Login or Register  to view this content.

[SHUTTEHFACE]

Thanks, tried the following but getting an error at

Please Login or Register  to view this content.

Please Login or Register  to view this content.

[stnkynts]

This is wrong

Please Login or Register  to view this content.

What is it supposed to be?

[SHUTTEHFACE]

I'm trying to define column Q and the row in which the current operation is taking place. If

Please Login or Register  to view this content.

Then the following cell needs to be equal to the cell located at column Q and the row modified.

[SHUTTEHFACE]

Any ideas?

[SHUTTEHFACE]

Please Login or Register  to view this content.

Getting "no cell were found" error at

Please Login or Register  to view this content.

Is this because the value of the cell is blank?

[stnkynts]

Using .Offset() how you are using it is incorrect. The reason my initial code included a .Offset(1) was to skip over the header by offsetting 1 row.

At this point I really have no idea what you are trying to do. I understand that the code is probably challenging to comprehend and you are trying to just smash your way through but unfortunately, you are not going to be able to do that.

What is this default schedule you previously referenced and where do you want it to go. Give some specific cell references.

Note: If you are wanting to learn I could probably write code in an easier to understand, but less efficient, manner. If this is the case let me know and I will attempt it if I have time.

[SHUTTEHFACE]

Thanks for the help. Let me try to be more clear and explain referencing the attached file: demo4.xlsm

You've written a code that changes the values in defined columns "CB", "EF", "GJ", "IN", "KR", "MV", "X", "OZ" based on the "Y" or "N" input in columns L:R. This works great. What I am trying to add is that if (for example) the cell L7 is "Y", CB7 becomes "WORK" and CC7 = Q7, CD7 = R7, CE7 = S7, CF7 = T7, CG7 = U7 where columns Q:U is the default schedule and CC:CG is the specific schedule for that day.

Please let me know if this is unclear. I like the efficiency of your code and would like to keep it that way.

Thanks!

[stnkynts]

Try this:

Please Login or Register  to view this content.

EDIT: The above code has been modified for improved performance

[SHUTTEHFACE]

Thanks man this works great! There is one last change I'd like to make if possible. Under the

Please Login or Register  to view this content.

tree, how would I make the cells in the range

Please Login or Register  to view this content.

(just like what what was listed under

Please Login or Register  to view this content.

) just equal "" (blank)?

[SHUTTEHFACE]

I tried

Please Login or Register  to view this content.

but it only clears the first column of the range selected as "N"

[stnkynts]

Instead of rCell.ClearContents try this:

Please Login or Register  to view this content.

[SHUTTEHFACE]

Thank you sir! You are ******* awesome.