Option Explicit
Sub coupon_loop()
Dim lrow As Long, i As Long
Dim fpath As String
Dim rDel As Range
Dim cell As Range
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
If .Cells(i, 8) = Cells(i, 54) And .Cells(i, 9) = .Cells(i, 55) Then GoTo GetNext
Worksheets("Selections").Range("B2").Value = .Range("D" & i).Value
Worksheets("Selections").Range("C2").Value = .Range("E" & i).Value
Worksheets("Selections").Range("E2").Value = .Range("AV" & 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
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)
Sheets("MarketsTemporary").Select
Set rDel = Nothing
With ActiveSheet.UsedRange
.Value = .Value
For Each cell In Intersect(.Cells, .Columns("A"))
If Len(cell.Text) = 0 Then
If rDel Is Nothing Then Set rDel = cell
Set rDel = Union(rDel, cell)
End If
Next cell
End With
If Not rDel Is Nothing Then rDel.EntireRow.Delete
Worksheets("Selections").Range("A2:AG356").Copy
Worksheets("SelectionsTemporary").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteValues)
Sheets("SelectionsTemporary").Select
Set rDel = Nothing
With ActiveSheet.UsedRange
.Value = .Value
For Each cell In Intersect(.Cells, .Columns("A"))
If Len(cell.Text) = 0 Then
If rDel Is Nothing Then Set rDel = cell
Set rDel = Union(rDel, cell)
End If
Next cell
End With
If Not rDel Is Nothing Then rDel.EntireRow.Delete
GetNext: Next i
End With
Sheets("EventsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("MarketsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
Sheets("SelectionsTemporary").Select
Columns("B:C").Select
Selection.Replace What:="_", Replacement:=" ", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
'Calculate Live Prices
Call calc_values
fpath = "C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
'Work C:\Documents and Settings\HOME USER\My Documents\Dropbox\_Work to be done\Footy Model csv"
'Laptop C:\Users\Adam\Dropbox\_Work to be done\Footy Model csv
'Saves Temporary Sheets as CSV's
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
'Clear contents of temporary worksheets
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
With Sheet2
.Range("H4", .Range("H4").End(xlDown).Offset(, 1)).Copy .Range("BB4")
End With
ThisWorkbook.Worksheets("Coupon").Activate
ThisWorkbook.Worksheets("Coupon").Range("A1").Select
Application.ScreenUpdating = True
Application.StatusBar = False
End Sub
I want to add an IF statement in there which checks if Sheet 2 Cell BD3 equals zero then end sub there. If not then run the code above.
Bookmarks