Tengo un código en el que básicamente estoy recorriendo y copiando datos de una hoja de Excel y pegándolos en mi libro de trabajo activo. Sin embargo, de la forma actual en que escribo mi código, estoy usando la declaración Exit Sub y todo lo que está debajo de esa declaración no se ejecutará. ¿Hay alguna alternativa que pueda utilizar a Exit Sub que seguirá funcionando con mi estructura de código actual?

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
0
a_js12 25 ago. 2020 a las 08:53

3 respuestas

La mejor respuesta

Sin Gosub/Return, como se indica en los comentarios, esa no es una forma "idiomática" de escribir en 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
2
Tim Williams 25 ago. 2020 a las 07:21

Mezcle los últimos bloques de código:

' <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
0
Gustav 25 ago. 2020 a las 06:38

No use un "goto" para eso, es mejor llamar a una función o sub.

Los "goto" son perfectos en vba para detectar errores

0
Javier Martin Gil 25 ago. 2020 a las 09:47