• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 265
  • Last Modified:

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
0
Stephen Byrom
Asked:
Stephen Byrom
3 Solutions
 
Jerry MillerCommented:
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
 
NorieCommented:
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
 
regmigrantCommented:
Range("G3:BR"&lastrow).Select
    Cells.Replace What:="Bad Piece", Replacement:="0", LookAt:=xlWhole, SearchOrder _
        :=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
0
 
Stephen ByromAuthor 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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now