Changing value of cells based on adjacent cell value - super messy code alert

[FionAwesome]

Hey folks,

I'm new to this forum so apologies in advance for anything I don't quite get right. I've read the forum rules but being autistic I'm not 100% on the social etiquette yet so I'll do my best.


My question is as such.


I have a spreadsheet which shows products (watches/jewellery) and the amount purchased but I need to discard certain products (for instance jewellery sets, loyalty points and watch warranties). I've done this by creating some VBA code that I mashed together (from code I found online) which looks for certain text in one column and then changes the number value of the adjacent column to 0.

The issue is that my code looks really horrible and super messy and I've effectively done a whole bunch of "nested ifs" (I think that's what they're called) for each parameter and I would like it to look slightly less messy.


I also have the issue that watch warranties are shown as "101" but one of our watches is shown as "5101" which means that I then have to go back and change all the values for "5101" back to their original value instead of "0".


I'll post the code I have so far and if anyone has any ideas to point me in the right direction I'd be super grateful!

Please Login or Register  to view this content.

Thanks in advance folks but if you need any more information then please let me know. I did want to copy some of the spreadsheet into here so you could see it visually but I'm not entirely sure how to do that yet. Haha.


Kind Regards

Fiona

[AlphaFrog]

You posted you first query perfectly, Fiona. You should teach others how to do it.

Try this...

Please Login or Register  to view this content.

[FionAwesome]

Oh thanks! I'll keep going as I am then!


The code is awesome thanks! It works for the most part, except that now cells with 101 in them aren't picked up by the code and so their values don't change. This helps me with the 5101 issue but I still need all the cells with 101 in them to change to 0. Is there any way of doing like "do the thing if the cell has the exact text 101 in it" and "do not do the thing if the cells has teh exact text 5101 in it"?

[AlphaFrog]

Change this...
"*[!5]101*"

To this...
"101"

[FionAwesome]

Fantastic! That works like a charm! Thanks so much!

Kind Regards