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!



Sub Sets()
    Dim x As Long
    For x = 1 To 65536
        If InStr(1, Sheet1.Range("$AG$" & x), "SET") > 0 Then
            Sheet1.Range("$AH$" & x) = "0"
        End If
        If InStr(1, Sheet1.Range("$AI$" & x), "SET") > 0 Then
            Sheet1.Range("$AJ$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AK$" & x), "SET") > 0 Then
            Sheet1.Range("$AL$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AM$" & x), "SET") > 0 Then
            Sheet1.Range("$AN$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AO$" & x), "SET") > 0 Then
            Sheet1.Range("$AP$" & x) = "0"
        End If
        If InStr(1, Sheet1.Range("$AG$" & x), "LOYALTY") > 0 Then
            Sheet1.Range("$AH$" & x) = "0"
        End If
        If InStr(1, Sheet1.Range("$AI$" & x), "LOYALTY") > 0 Then
            Sheet1.Range("$AJ$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AK$" & x), "LOYALTY") > 0 Then
            Sheet1.Range("$AL$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AM$" & x), "LOYALTY") > 0 Then
            Sheet1.Range("$AN$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AO$" & x), "LOYALTY") > 0 Then
            Sheet1.Range("$AP$" & x) = "0"
        End If
        If InStr(1, Sheet1.Range("$AG$" & x), "101") > 0 Then
            Sheet1.Range("$AH$" & x) = "0"
        End If
        If InStr(1, Sheet1.Range("$AI$" & x), "101") > 0 Then
            Sheet1.Range("$AJ$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AK$" & x), "101") > 0 Then
            Sheet1.Range("$AL$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AM$" & x), "101") > 0 Then
            Sheet1.Range("$AN$" & x) = "0"
        End If
         If InStr(1, Sheet1.Range("$AO$" & x), "101") > 0 Then
            Sheet1.Range("$AP$" & x) = "0"
        End If
    Next
End Sub


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