Solved

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

Posted on 2014-03-01
4
256 Views
Last Modified: 2014-03-01
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
Comment
Question by:Stephen Byrom
4 Comments
 
LVL 18

Assisted Solution

by:Jerry Miller
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

by:
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

Open in new window

0
 
LVL 19

Assisted Solution

by:regmigrant
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

by:Stephen Byrom
ID: 39897425
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Convert between Excel file formats (.XLS, .XLSX, .XLSM) with/without macro option David Miller (dlmille) Intro Over this past Fall, I've had the opportunity to see several similar requests and have developed a couple related solutions associate…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question