Results 1 to 4 of 4

VBA Exit Sub

This is a discussion on VBA Exit Sub within the Excel Questions forums, part of the Question Forums category; Help... The code below works fine, yet when the condition If Range("X" & X).Value = "Short" And Range("Y" & X).Value ...

  1. #1
    Board Regular
    Join Date
    Dec 2008
    Posts
    228

    Default VBA Exit Sub

    Help...

    The code below works fine, yet when the condition If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
    MsgBox "You must enter the responsible owner for all shortforms prior exporting." Is true I dont want the following code to run. I want it to exit sub. If it's not true then I want it to run. Any ideas???????

    Dim NewFileName
    Dim X As Variant
    For X = 6 To 33
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
    MsgBox "You must enter the responsible owner for all shortforms prior exporting."
    Next
    Sheets("All Trains").Select
    Sheets("All Trains").Copy
    Cells.Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
    False, Transpose:=False
    Application.CutCopyMode = False
    Rows("47:54").Select
    Selection.Delete Shift:=xlUp
    Range("B1").Select

    NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _
    Format$(Range("C3").Value, "ddmmmyy") & ".xls"
    For Each ws In Worksheets
    ActiveSheet.Name = Format(Range("C3").Value, "yymmdd")
    Next
    ActiveWorkbook.SaveAs Filename:=NewFileName
    ActiveWorkbook.Close

    MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3")

    Application.DisplayAlerts = True
    Application.ScreenUpdating = True


    Many thanks in advance...

  2. #2
    Board Regular bobsan42's Avatar
    Join Date
    Jul 2010
    Location
    Bulgaria, GMT+2 (42.891813,25.313594)
    Posts
    587

    Default Re: VBA Exit Sub

    Code:
    Dim NewFileName
    Dim X As Variant
    For X = 6 To 33
    If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then 
    MsgBox "You must enter the responsible owner for all shortforms prior exporting."
    Exit Sub
    End if
    Next
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    Sheets("All Trains").Select
        Sheets("All Trains").Copy
        Cells.Select
        Selection.Copy
        Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
            False, Transpose:=False
        Application.CutCopyMode = False
        Rows("47:54").Select
        Selection.Delete Shift:=xlUp
        Range("B1").Select
     
         NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _
        Format$(Range("C3").Value, "ddmmmyy") & ".xls"
        For Each ws In Worksheets
    ActiveSheet.Name = Format(Range("C3").Value, "yymmdd")
    Next
        ActiveWorkbook.SaveAs Filename:=NewFileName
        ActiveWorkbook.Close
        
        MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3")
        
        Application.DisplayAlerts = True
        Application.ScreenUpdating = True
    Last edited by bobsan42; Aug 24th, 2010 at 08:10 AM.
    "...it's sad that in our blindness we gather thorns for flowers..."
    winXPSP3/excel 3x2003 at work, 2003+2x2007 at home

  3. #3
    Board Regular
    Join Date
    Feb 2010
    Location
    London, UK
    Posts
    4,892

    Default VBA Exit Sub

    Try changing
    Code:
    If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then _
    MsgBox "You must enter the responsible owner for all shortforms prior exporting."
    to
    Code:
    If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then
    MsgBox "You must enter the responsible owner for all shortforms prior exporting."
    Exit Sub
    End If


  4. #4
    Board Regular
    Join Date
    Dec 2008
    Posts
    228

    Default Re: VBA Exit Sub

    Many thanks The code below worked a treat....Thanks again...Dim NewFileNameDim X As VariantFor X = 6 To 33If Range("X" & X).Value = "Short" And Range("Y" & X).Value = "" Then MsgBox "You must enter the responsible owner for all shortforms prior exporting."Exit SubEnd ifNextApplication.DisplayAlerts = FalseApplication.ScreenUpdating = FalseSheets("All Trains").Select Sheets("All Trains").Copy Cells.Select Selection.Copy Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Application.CutCopyMode = False Rows("47:54").Select Selection.Delete Shift:=xlUp Range("B1").Select NewFileName = "O:\_First Performance\Shortforms\Short Form Regime\Daily Reports\FW30 Daily Formation File-" & _ Format$(Range("C3").Value, "ddmmmyy") & ".xls" For Each ws In WorksheetsActiveSheet.Name = Format(Range("C3").Value, "yymmdd")Next ActiveWorkbook.SaveAs Filename:=NewFileName ActiveWorkbook.Close MsgBox "FGW30 Daily Formation File complete for the" & " - " & Range("C3") Application.DisplayAlerts = True Application.ScreenUpdating = True

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com