CODEWITHSUNDEEP
excelvba
a_js12
a_js12

Reputation: 329

Using The Exit Sub Statement in VBA

I have a code where I'm basically looping through and copying data from one excel sheet and pasting it into my active workbook. However, the current way I have my code written I'm using the Exit Sub statement and everything below that statement will not execute. Is there an alternative I can use to the Exit Sub which will still work with my current code structure?

Dim ws As Worksheet

Dim Prefix As String, Suffix As String
Dim ROfs As Integer, COfs As Integer
Dim Source As Range, Dest As Range, This As Range


Set ws = ThisWorkbook.Worksheets("Sheet1")



  With ws
    'The formula becomes Prefix & Source.Address & Suffix
    Prefix = "='C:x\xx\[Workbook1.xls]Worksheet1'!"
    Suffix = "/1000"
    'Direction for the next Source cell
    COfs = -1

    'Destination cells
    Set Dest = .Range("G8:G12")
    Set Source = Range("G8")
    GoSub Fill

    Set Dest = .Range("G13:G17")
    Set Source = Range("G9")
    GoSub Fill
    
    Suffix = ""
    Set Dest = .Range("G3:G7")
    Set Source = Range("G10")
    GoSub Fill

    Suffix = "/1000"
    Set Dest = .Range("G26:G30")
    Set Source = Range("G35")
    GoSub Fill
   
    Suffix = ""
    Set Dest = .Range("G21:G25")
    Set Source = Range("G37")
    GoSub Fill
   
    Suffix = "/1000"
    Set Dest = .Range("G31:G35")
    Set Source = Range("G36")
    GoSub Fill
    
  End With
  
Exit Sub
 
Fill:
  For Each This In Dest
    This.Formula = Prefix & Source.Address(0, 0) & Suffix
    Set Source = Source.Offset(ROfs, COfs)
  Next
  Dest.Value = Dest.Value
  Return

'''Everything Below Does Not Execute'''

ws.Columns("C").EntireColumn.Delete

Dim strFormulas As Variant

    With ws
         strFormulas = "=(F3-C3)"
        .Range("G3:G17").Formula = strFormulas
        .Range("G3:G17").FillDown
   End With

End Sub

Upvotes: 0

Views: 138

Answers (3)

Javier Martin Gil
Javier Martin Gil

Reputation: 36

Dont use a "goto" for that, is better to call a function or sub.

The "goto" are perfect in vba for catch errors

Upvotes: 0

Tim Williams
Tim Williams

Reputation: 166306

Without Gosub/Return - as noted in the comments, that's not an "idiomatic" way to write in VBA

Sub Test()
    
    Dim prefix As String, suffix As String
    
    With ThisWorkbook.Worksheets("Sheet1")
        'The formula becomes Prefix & Source.Address & Suffix
        prefix = "='C:x\xx\[Workbook1.xls]Worksheet1'!"
        suffix = "/1000"
        
        'Destination cells
        DoFill Range("G8"), .Range("G8:G12"), prefix, suffix
        DoFill Range("G9"), .Range("G13:G17"), prefix, suffix
        DoFill Range("G10"), .Range("G3:G7"), prefix, ""
        DoFill Range("G35"), .Range("G26:G30"), prefix, suffix
        DoFill Range("G37"), .Range("G21:G25"), prefix, ""
        DoFill Range("G36"), .Range("G31:G35"), prefix, suffix
    
        .Columns("C").EntireColumn.Delete
        .Range("G3:G17").Formula = "=(F3-C3)"
    End With
      
End Sub

Sub DoFill(src As Range, dest As Range, prefix As String, suffix As String)
      Dim c As Range
      For Each c In dest
        c.Formula = prefix & src.Address(0, 0) & suffix
        Set src = src.Offset(0, -1)
      Next
      dest.Value = dest.Value
End Sub

Upvotes: 2

Gustav
Gustav

Reputation: 55831

Shuffle the last code blocks:

' <snip>

  End With
  
ws.Columns("C").EntireColumn.Delete

Dim strFormulas As Variant

    With ws
         strFormulas = "=(F3-C3)"
        .Range("G3:G17").Formula = strFormulas
        .Range("G3:G17").FillDown
   End With

Exit Sub
 

Fill:
  For Each This In Dest
    This.Formula = Prefix & Source.Address(0, 0) & Suffix
    Set Source = Source.Offset(ROfs, COfs)
  Next
  Dest.Value = Dest.Value
  Return

End Sub

Upvotes: 0

Related Questions