VBA Code - If cell value = "xxx" then cell value = "yyy"

[honger]

I currently have this formula for each cell in a column J

=IF(F3="Content Final","Ready for Processing","") and so on

But i would like this to be run off of VBA code since i do not want users to delete the formula in each cell. Anybody can help me with a code?

this is the current code i am trying out to no avail.

Please Login or Register  to view this content.

[ajryan88]

Hi,

Try using the Worksheet_Change event instead of the Worksheet_Calculate event:

Please Login or Register  to view this content.

Hope this helps

[honger]

thank you very much for you help, but i am getting this message?

2-21-2014 12-43-31 AM.png

[ajryan88]

Could you please attach your workbook...?

[honger]

Could you please attach your workbook...?

Draft-TOC-(Product)-(Applicatiion_Number)-(SCN).xlsm

here is the attached file

[honger]

so basical;ly if i choose values in column F to content final, then column J should be Ready for processing.....

thanks

[honger]

oh and password would be 'sub2011' if you need to access them

[ajryan88]

First of all, I figured you wanted it to be a comment, so in the code that I gave you, change

Please Login or Register  to view this content.

to

Please Login or Register  to view this content.

Secondly, try unprotecting the VBA and run the macro again, and tell me if you get the same error message.

[ajryan88]

Delete your Worksheet_Change subroutine and replace it with mine. I believe the error is occurring because you have multiple Worksheet_Change subroutines declared. Also, make the change that I mentioned in my previous post to have the cell value change rather than the cell comment.

Let me know

[honger]

thank you very much for all your hep. but i am getting this message now? and its freezingout-of-stack.png but the value did change to "ready for processing" its just that it keeps freezing.

i really appreciate all your help, it is 1 am my time and i am hitting the bed. Hope to see your answer tomorrow morining once i awake.

[ajryan88]

All fixed.

This error was occurring because the procedure was being called again once we changed the value in column J, and Excel doesn't like it when a procedure calls itself.

The solution was to disable events while the value in column J was changed.

Hope this helps

[ajryan88]

All fixed.

This error was occurring because the procedure was being called again once we changed the value in column J, and Excel doesn't like it when a procedure calls itself.

The solution was to disable events while the value in column J was changed.

Hope this helps

[ajryan88]

Sorry about the duplicate post, had a problem with the site and it uploaded twice

[honger]

thank yuo very much! this forum rocks

[honger]

Ajryan, sorry to bother you again, but i am now trying to offset the column to the left. So basically if values of cells in column J is "Reset to Draft" then cells in column F will equal "Draft"

This is the current code i have but nothing is happening?

Please Login or Register  to view this content.

[honger]

i figured it out.

Thanks again AJ

[ajryan88]

You're welcome.

It isn't working because there can only be one Worksheet_Change event, so you need to combine your code into the existing subroutine rather than creating a new one:

Please Login or Register  to view this content.

Give me 5 minutes and I'll rewrite your code so that it is cleaner and easier to read, and I'll post the changed code back here

[ajryan88]

Here you go, use this subroutine instead of the last one that I posted. Hope this helps

Please Login or Register  to view this content.

[ajryan88]

No problems, please don't forget to mark this thread as solved

[honger]

Thank u!!!