Solved

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

Posted on 2014-03-01
257 Views
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
0
Question by:Stephen Byrom

LVL 18

Assisted Solution

Jerry Miller earned 50 total points
ID: 39897285
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

LVL 33

Accepted Solution

Norie earned 350 total points
ID: 39897325
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
0

LVL 19

Assisted Solution

regmigrant earned 100 total points
ID: 39897376
Range("G3:BR"&lastrow).Select
Cells.Replace What:="Bad Piece", Replacement:="0", LookAt:=xlWhole, SearchOrder _
:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
0

LVL 1

Author Closing Comment

ID: 39897425
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!