Loop within a loop within a loop......

Hi,
I have tried doing loops within loops but it's driving me nuts (or loopy!)

basically I have a sheet that gets copied each week from a server. The data I do sumifs on, is only in certain columns, but I need to change any quantities of bad pieces to 0 so as not to count them.

I have loops that do the job but it's very messy and I have been trying for some time without success to nest the loops.

The code I have that works is
        lastrow = WS1.Range("A1048576").End(xlUp).Row

        For a = lastrow To 3 Step -1
            If WS1.Range("G" & a).Value = "BAD PIECE" Then
                WS1.Range("G" & a).Offset(0, 1) = 0
            End If
        Next a
        
        For b = lastrow To 3 Step -1
            If WS1.Range("N" & b).Value = "BAD PIECE" Then
                WS1.Range("N" & b).Offset(0, 1) = 0
            End If
        Next b
        
        For c = lastrow To 3 Step -1
            If WS1.Range("U" & c).Value = "BAD PIECE" Then
                WS1.Range("U" & c).Offset(0, 1) = 0
            End If
        Next c
        
        For d = lastrow To 3 Step -1
            If WS1.Range("AB" & d).Value = "BAD PIECE" Then
                WS1.Range("AB" & d).Offset(0, 1) = 0
            End If
        Next d
        
        For e = lastrow To 3 Step -1
            If WS1.Range("AI" & e).Value = "BAD PIECE" Then
                WS1.Range("AI" & e).Offset(0, 1) = 0
            End If
        Next e
       
        For f = lastrow To 3 Step -1
            If WS1.Range("AP" & f).Value = "BAD PIECE" Then
                WS1.Range("AP" & f).Offset(0, 1) = 0
            End If
        Next f
        
        For g = lastrow To 3 Step -1
            If WS1.Range("AW" & g).Value = "BAD PIECE" Then
                WS1.Range("AW" & g).Offset(0, 1) = 0
            End If
        Next g
        
        For h = lastrow To 3 Step -1
            If WS1.Range("BD" & h).Value = "BAD PIECE" Then
                WS1.Range("BD" & h).Offset(0, 1) = 0
            End If
        Next h
        
        For i = lastrow To 3 Step -1
            If WS1.Range("BK" & i).Value = "BAD PIECE" Then
                WS1.Range("BK" & i).Offset(0, 1) = 0
            End If
        Next i
        
        For j = lastrow To 3 Step -1
            If WS1.Range("BR" & j).Value = "BAD PIECE" Then
                WS1.Range("BR" & j).Offset(0, 1) = 0
            End If
        Next j

Open in new window

but as I say....messy.

Any help as always is much appreciated
LVL 1
Stephen ByromWarehouse/ShippingAsked:
Who is Participating?
 
NorieConnect With a Mentor VBA ExpertCommented:
Try this.
For I = 3 To LastRow
    For J = 7 To 70 Step 7
        If WS1.Cells(I, J).Value = "BAD PIECE" Then
                WS1.Cells(I,J+1).Value =  0
        End If
    Next J
Next I

Open in new window

0
 
Jerry MillerConnect With a Mentor Commented:
This is a little cleaner and you only have one FOR loop.

lastrow = WS1.Range("A1048576").End(xlUp).Row

        For a = lastrow To 3 Step -1
     If WS1.Range("G" & a).Value = "BAD PIECE" Then
                WS1.Range("G" & a).Offset(0, 1) = 0
           
       ElseIf WS1.Range("N" & a).Value = "BAD PIECE" Then
                WS1.Range("N" & a).Offset(0, 1) = 0
           
      ElseIf WS1.Range("U" & a).Value = "BAD PIECE" Then
                WS1.Range("U" & a).Offset(0, 1) = 0
     
        ElseIf WS1.Range("AB" & a).Value = "BAD PIECE" Then
                WS1.Range("AB" & a).Offset(0, 1) = 0
         
       ElseIf WS1.Range("AI" & a).Value = "BAD PIECE" Then
                WS1.Range("AI" & a).Offset(0, 1) = 0
         
       ElseIf WS1.Range("AP" & a).Value = "BAD PIECE" Then
                WS1.Range("AP" & a).Offset(0, 1) = 0
           
         ElseIf WS1.Range("AW" & a).Value = "BAD PIECE" Then
                WS1.Range("AW" & a).Offset(0, 1) = 0
         
        ElseIf WS1.Range("BD" & a).Value = "BAD PIECE" Then
                WS1.Range("BD" & a).Offset(0, 1) = 0
           
        ElseIf WS1.Range("BK" & a).Value = "BAD PIECE" Then
                WS1.Range("BK" & a).Offset(0, 1) = 0
           
       ElseIf WS1.Range("BR" & a).Value = "BAD PIECE" Then
                WS1.Range("BR" & a).Offset(0, 1) = 0
     Next a
0
 
regmigrantConnect With a Mentor Commented:
Range("G3:BR"&lastrow).Select
    Cells.Replace What:="Bad Piece", Replacement:="0", LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
0
 
Stephen ByromWarehouse/ShippingAuthor Commented:
Thanks for the answers people.
They all work in their own way, but the lion's share had to go to imnorie's great loop routine.
Thanks again
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.