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

Stephen Byrom used Ask the Experts™
on
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
``````
but as I say....messy.

Any help as always is much appreciated
Comment
Watch Question

Do more with

EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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
Analyst Assistant
Commented:
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
``````
Commented:
Range("G3:BR"&lastrow).Select
Cells.Replace What:="Bad Piece", Replacement:="0", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
Warehouse/Shipping

Commented: