VBA Help - Properly Exit Sub

[Johnny Thunder]

Hi Group,

Working on a script that is doing a copy/paste type function and looping down a list of text to update a report.

I have the code working fine, but when I come across a blank cell I would like the macro to EXIT SUb, the problem that I am having is that I have code after the Exit sub line that calls other macros if a specific cell has text, My question is, how can I rewrite my code so that the Macro REALLY closes out and exits the sub without doing anymore?

I tried the GOTO approach but I obviously didn't know what I was doing because it didn't work.

My code below:

Sub Copy_CCodes()
    
    Dim lastRow As Long
    Dim Datastore As Worksheet, FinalDest As Worksheet
    Dim i As Range


    Set Datastore = Sheets("Lookups2")
    Set FinalDest = Sheets("Summary")


    lastRow = Datastore.Cells(Rows.Count, "L").End(xlUp).Row
    Set rng = Datastore.Range("CCodes")
             
  Application.ScreenUpdating = False
  Application.DisplayAlerts = False
  
  Call FindRep
  
  Datastore.Activate
  Range("N2").Select
  
  'Set Do loop to stop when two consecutive empty cells are reached.
      If FinalDest.Range("A9").Value = "" Then
      
      GoTo KillCode
        
        'ActiveCell.Offset(1, 0).Select
            
            Else
      
    'Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
    Do Until IsEmpty(ActiveCell)
         ActiveCell.Copy
              FinalDest.Range("RPTCC").PasteSpecial Paste:=xlValues
                    Calculate
           
    Call SaveSheet
                
         ' Step down 1 rows from present location.
         ActiveCell.Offset(1, 0).Select
      Loop
  
  End If
  
KillCode:
  
  Exit Sub
 
  
Call CountFiles
   
End Sub

 

[shg]

This could be rewritten to avoid the selecting, but for a first pass,

Sub Copy_CCodes()
  Dim lastRow       As Long
  Dim Datastore     As Worksheet
  Dim FinalDest     As Worksheet

  Set Datastore = Sheets("Lookups2")
  Set FinalDest = Sheets("Summary")

  lastRow = Datastore.Cells(Rows.Count, "L").End(xlUp).Row

  Call FindRep

  Datastore.Activate
  Range("N2").Select

  If FinalDest.Range("A9").Value <> "" Then
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do Until IsEmpty(ActiveCell)
      FinalDest.Range("RPTCC").Value = ActiveCell.Value
      Calculate

      Call SaveSheet

      ActiveCell.Offset(1, 0).Select
    Loop
    
    Call CountFiles
    Application.ScreenUpdating = True
  End If
End Sub

 

[Johnny Thunder]

Thank you for the help on this Shg, I have re-ran with your code and I am still getting the same issue.

This could be rewritten to avoid the selecting, but for a first pass, the reason I need the code to die after a blank cell is reached is due to the call of the Savesheet macro.

Currently, with your updates, the code still calls the SaveSheet Macro even if a blank cell is reached on this line * If FinalDest.Range("A9").Value <> "" Then

I was hoping that when the loop hits a blank cell, it discontinues all activity and does not run any other code after.

Sub Copy_CCodes()
  Dim lastRow       As Long
  Dim Datastore     As Worksheet
  Dim FinalDest     As Worksheet

  Set Datastore = Sheets("Lookups2")
  Set FinalDest = Sheets("Summary")

  lastRow = Datastore.Cells(Rows.Count, "L").End(xlUp).Row

  Call FindRep

  Datastore.Activate
  Range("N2").Select

  If FinalDest.Range("A9").Value <> "" Then
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False

    Do Until IsEmpty(ActiveCell)
      FinalDest.Range("RPTCC").Value = ActiveCell.Value
      Calculate

      Call SaveSheet

      ActiveCell.Offset(1, 0).Select
    Loop
    
    Call CountFiles
    Application.ScreenUpdating = True
  End If
End Sub

 

[mikerickson]

Try

FinalDest.Range("A9").Value = "" Then
    Exit Sub
Else
    ' current code
End If

 

[Johnny Thunder]

Thanks Mike, tried that already. The problem I am having is that after the code does Exit Sub, it still continues to next argument below which is the Call SaveSheet macro.

Try

FinalDest.Range("A9").Value = "" Then
    Exit Sub
Else
    ' current code
End If

 

[mikerickson]

It shouldn't do that, since the Call SaveSheet should be inside the Else branch.

 

[mikerickson]

Step through the code, if it is hitting the Exit Sub line at the right time, keep stepping to see where it's going.
Or replace Exit Sub with END.

 

[shg]

Or replace Exit Sub with END.

That's a rather large hammer ...

 

[Johnny Thunder]

Ok, I just figured out what the issue was.

Thank you Shg for the condensed code and Mike for the suggestion about the Stepping thru, I found that I was do the If Statement before I updated the cell that would contain the Blank.

I am however curious Shg, how would I rewrite to not have the SELECT lines of code?

New Code:

Sub Copy_CCodes()
  
  Dim lastRow       As Long
  Dim Datastore     As Worksheet
  Dim FinalDest     As Worksheet


  Set Datastore = Sheets("Lookups2")
  Set FinalDest = Sheets("Summary")


  lastRow = Datastore.Cells(Rows.Count, "N").End(xlUp).Row


  Call FindRep


  Datastore.Activate
  Range("N2").Select


  If ActiveCell.Value = "" Then
        Exit Sub
            Application.ScreenUpdating = False
                Application.DisplayAlerts = False
                
                Else


    Do Until IsEmpty(ActiveCell)
      FinalDest.Range("RPTCC").Value = ActiveCell.Value
      Calculate


      Call SaveSheet


      ActiveCell.Offset(1, 0).Select
    Loop
    
    Call CountFiles
    Application.ScreenUpdating = True
  End If
End Sub

 

[mikerickson]

That's a rather large hammer ...

Large hammers make really flat bugs.
@Johnny Thunder, stepping through the code is a very useful technique for debugging. I'm glad it worked for you.