Skip loop if cells equal same values

**adam2308** · 07-01-2012, 02:32 PM

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?

**xladept** · 07-01-2012, 02:58 PM

Hi Adam2308,

Maybe:

With Worksheets("Coupon")

    lrow = .Range("D" & .Rows.Count).End(xlUp).Row
    For i = 4 To lrow
    
If  .Range(“J” & i)=.Range(“BB” & i) AND .Range(“K” & i)=.Range(“BC” & i) 
Then GoTo GetNext: End If

        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)
    

   GetNext: Next i
End With

**adam2308** · 07-01-2012, 03:57 PM

Thank you xladept.

Firstly, I changed

If  .Range(“J” & i)=.Range(“BB” & i) AND .Range(“K” & i)=.Range(“BC” & i)
Then GoTo GetNext: End If

to all be on the same line as it was highlighted as red font and didn't seem to work this way. Let me know if I shouldn't have done this.

I then copied your code over mine and I am getting a "compile error: Variable not defined" and it highlights "J" in the line of code

If .Range(“J” & i) = .Range(“BB” & i) And .Range(“K” & i) = .Range(“BC” & i) Then GoTo GetNext: End If

**xladept** · 07-01-2012, 04:01 PM

Hi Adam 2308,

It's Ok to have it on one line but remove the colon and End If.

**adam2308** · 07-01-2012, 04:04 PM

Done that. So the line of code now reads

If .Range(“J” & i) = .Range(“BB” & i) And .Range(“K” & i) = .Range(“BC” & i) Then GoTo GetNext

But still getting the same compile error message.

**xladept** · 07-01-2012, 04:08 PM

Have you put the GetNext procedure before the Next i?

**adam2308** · 07-01-2012, 04:13 PM

Still getting the same compile error without dots in ".range"

**xladept** · 07-01-2012, 04:18 PM

If .Cells(i,10)=Cells(i,54) AND .Cells(i,11)=.Cells(i,55) Then GoTo GetNext

Try this line.

**adam2308** · 07-01-2012, 05:04 PM

ok, we have progress in that line of code now works but the code overall doesn't do what I was looking for.

I have attached a workbook to try and show you more clearly. If you click CommandButton2 then it will run the code we are discussing. Now, in this example, the code we changed should mean that only the first match (Arseanl v Bolton) on the 'coupon' is process because all the other matches have the same values in columns J & K as BB & BC. However, all matches are processed and copied on to the csv files created. The csv files on this example should only contain the Arsenal v Bolton match.

**xladept** · 07-01-2012, 06:48 PM

Hey Adam2308,

I tested your workbook and our code but by just testing the J vs BB values there were no matches in your sample.

BTW the .Range code works as well as the .Cells code on my machine - perhaps there was something else going on.

Here's how I tested it:

Sub coupon_loop(): Dim S As String, T As String
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
    
     S = .Cells(i, 10): T = .Cells(i, 54)
    
If .Cells(i, 10) = .Cells(i, 54) And .Cells(i, 11) = .Cells(i, 55) Then GoTo GetNext

**adam2308** · 07-02-2012, 09:38 AM

Sorry, I gave you the wrong columns! It should have been H and I!!! Works perfectly now.

Note to self - Don't work on projects with an hangover!

**xladept** · 07-02-2012, 01:19 PM

Great, I'm glad to have helped.

Skip loop if cells equal same values

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Re: Skip loop if cells equal same values

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions