Results 1 to 12 of 12

Skip loop if cells equal same values

Threaded View

  1. #1
    Forum Contributor
    Join Date
    02-20-2009
    Location
    Manchester, England
    MS-Off Ver
    Excel 2007
    Posts
    467

    Skip loop if cells equal same values

    I currently have the code below that runs from row 4 in worksheet "Coupon" until it reaches the last last non-blank row.

    Option Explicit
    
    Sub coupon_loop()
    
    Dim lrow As Long, i As Long
    Dim fpath As String
    
    Application.ScreenUpdating = False
    Application.DisplayStatusBar = True
    Application.StatusBar = "Creating CSV Files"
    
    
    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 4 To lrow
        
    
    
            Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
            Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
            Worksheets("Selections").Range("E2").Value = .Range("BA" & i).Value
            Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
            Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
            Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
            Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
            Worksheets("Markets").Range("AB2:AB83").Value = .Range("V" & i).Value
        
            Call calc_values
        
    Worksheets("Events").Range("A2:W2").Copy
    Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Markets").Range("A2:AB83").Copy
    Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Selections").Range("A2:U356").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        
    
        Next i
    End With
    
    fpath = "C:\Documents and Settings\HOME USER\My Documents"
    
    
    ThisWorkbook.Worksheets("EventsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Events - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    ThisWorkbook.Worksheets("MarketsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Markets - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    ThisWorkbook.Worksheets("SelectionsTemporary").Copy
    ActiveWorkbook.SaveAs Filename:=fpath & "\Selections - " & Format(Now, "yyyy-mm-dd hh-mm-ss") & ".csv", FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close SaveChanges:=True
    
    lrow = ThisWorkbook.Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("EventsTemporary").Range("A2:W" & lrow).ClearContents
    
    lrow = ThisWorkbook.Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("MarketsTemporary").Range("A2:AD" & lrow).ClearContents
    
    lrow = ThisWorkbook.Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Row
    ThisWorkbook.Worksheets("SelectionsTemporary").Range("A2:Y" & lrow).ClearContents
    
    ThisWorkbook.Worksheets("Coupon").Activate
    ThisWorkbook.Worksheets("Coupon").Range("A1").Select
    
    
    Application.ScreenUpdating = True
    Application.StatusBar = False
    
    End Sub
    I want to make a tweak to this so that it is more optimized, not doing unnecessary work any runs much more quickly. What I would like to do is to only run this part of the code

    With Worksheets("Coupon")
    
        lrow = .Range("D" & .Rows.Count).End(xlUp).Row
        For i = 4 To lrow
        
    
    
            Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
            Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
            Worksheets("Selections").Range("E2").Value = .Range("BA" & i).Value
            Worksheets("Selections").Range("Z2").Value = .Range("J" & i).Value
            Worksheets("Selections").Range("Z4").Value = .Range("K" & i).Value
            Worksheets("Selections").Range("G2").Value = .Range("F" & i).Value
            Worksheets("Selections").Range("G4").Value = .Range("G" & i).Value
            Worksheets("Markets").Range("AB2:AB83").Value = .Range("V" & i).Value
        
            Call calc_values
        
    Worksheets("Events").Range("A2:W2").Copy
    Worksheets("EventsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Markets").Range("A2:AB83").Copy
    Worksheets("MarketsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
    
    Worksheets("Selections").Range("A2:U356").Copy
    Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
        
    
        Next i
    End With
    If the values in columns J and K are not equal to columns BB and BC. If they are then skip this loop and move on to the next row.

    Does this all make sense?
    Last edited by adam2308; 07-02-2012 at 09:39 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1