Solved

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

Posted on 2014-03-01
4
255 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

947 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now