+ Reply to Thread
Results 1 to 2 of 2

End Sub if cell greater than zero

Hybrid View

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

    End Sub if cell greater than zero

    I currently have the following code:

    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.

    Apologies the thread title is misleading. It should be End Sub if cell equals zero

    Thanks,
    Adam.
    Last edited by adam2308; 07-25-2012 at 12:02 PM. Reason: Title error

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,645

    Re: End Sub if cell greater than zero

    Use
    if range("Bd3").value = 0 then exit sub

+ Reply to Thread

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