如何在應用“If-then”之前或應用“If-then”之后執行命令/宏？

'Membuat shortcut sheet untuk lebih pendek
'Make shortcut
Set po = Sheets("Print Out")
Dim awal, Akhir As Integer
Dim j As Long
'mencantumkan N6 & O6 menjadi value acuan
'Make N6 & O6 as main value print
awal = po.Range("N6").Value
Akhir = po.Range("O6").Value
j = 0
'menjalankan menu pilih printer
'Show up print dialog and set "normal" print area (w/o) condition
Application.ScreenUpdating = False
Application.Dialogs(xlDialogPrinterSetup).Show
po.PageSetup.PrintArea = "$B$1:$L$57"
'perintah utama
'Main command to apply auto mass print
    For i = awal To Akhir
        With po
            .Range("M1").Value = i   0   j
            .Range("M2").Value = i   1   j
            .Range("M3").Value = i   2   j
            .Range("M4").Value = i   3   j
            .PrintPreview
            j = j   3
        End With
'jika mendeteksi N/A atau sejenisnya perintah akan berhenti
'If found #N/A or #REF or any error loop will stop to prevent loop printing even data already reach limit
      If IsError(po.Range("C4")) Then Exit For
      If IsError(po.Range("C18")) Then
      po.PageSetup.PrintArea = "$B$1:$L$15"
      Exit For
'Jika M4 lebih besar dari batas akhir (O6) maka perintah akan terhenti
'If m4 > than O6 then print area will change and stop the loop (THIS IS THE PROBLEM)
      If po.Range("M4")   2 > po.Range("O6") Then po.PageSetup.PrintArea = "$B$1:$L$43"
          If po.Range("M3")   3 > po.Range("O6") Then po.PageSetup.PrintArea = "$B$1:$L$29"
            If po.Range("M2")   4 > po.Range("O6") Then po.PageSetup.PrintArea = "$B$1:$L$15"
    Next i

我如上創建的宏，但有一個我無法克服的問題。

像下面一行：

If po.Range("M4") 2 > po.Range("O6") Then po.PageSetup.PrintArea = "$B$1:$L$43"

基本上，如果上面的代碼超過了我設定的限制，它將調整“printarea”，但我想添加一個“Exit for”命令以在滿足“If-then”條件后停止回圈。`

我已經嘗試過

If po.Range("M4")   2 > po.Range("O6") Then 
po.PageSetup.PrintArea = "$B$1:$L$43
Exit for

但是“退出”代碼將與 printarea 更改一起運行，因此我的最后一頁尚未列印。我想要的是在我的最后一頁列印后停止回圈（我的最后一頁將始終與上面的“如果那么”相關）。

uj5u.com熱心網友回復：

If...Then和Select Case

Option Explicit

Sub Test()
    'Membuat shortcut sheet untuk lebih pendek
    'Make shortcut
    
    ' Reference (set) the workbook.
    Dim wb As Workbook: Set wb = ThisWorkbook ' workbook containing this code
    ' Reference (set) the worksheet.
    Dim po As Worksheet: Set po = wb.Worksheets("Print Out")
    
    With po
    
        'mencantumkan N6 & O6 menjadi value acuan
        'Make N6 & O6 as main value print
        Dim Awal As Long: Awal = .Range("N6").Value
        Dim Akhir As Long: Akhir = .Range("O6").Value
    
        'menjalankan menu pilih printer
        'Show up print dialog and set "normal" print area (w/o) condition
        Application.ScreenUpdating = False
        Application.Dialogs(xlDialogPrinterSetup).Show
        .PageSetup.PrintArea = "$B$1:$L$57"
        
        Dim i As Long
        Dim j As Long
        
        'perintah utama
        'Main command to apply auto mass print
        For i = Awal To Akhir
            
            .Range("M1").Value = i   0   j
            .Range("M2").Value = i   1   j
            .Range("M3").Value = i   2   j
            .Range("M4").Value = i   3   j
            .PrintPreview
            j = j   3
            
            'jika mendeteksi N/A atau sejenisnya perintah akan berhenti
            'If found #N/A or #REF or any error loop will stop to prevent loop printing even data already reach limit
            If IsError(.Range("C4")) Then
                ' do nothing!
            ElseIf IsError(.Range("C18")) Then
                .PageSetup.PrintArea = "$B$1:$L$15"
            Else
                'Jika M4 lebih besar dari batas akhir (O6) maka perintah akan terhenti
                'If m4 > than O6 then print area will change and stop the loop (THIS IS THE PROBLEM)
                Select Case .Range("O6").Value
                    ' This logic is wrong! All cases are the same:
                    ' .Range("M4")   2 = .Range("M3")   3 = .Range("M2")   4
                    Case Is < .Range("M4").Value   2: .PageSetup.PrintArea = "$B$1:$L$43"
                    Case Is < .Range("M3").Value   3: .PageSetup.PrintArea = "$B$1:$L$29"
                    Case Is < .Range("M2").Value   4: .PageSetup.PrintArea = "$B$1:$L$15"
                    Case Else
                        ' do nothing!?...
                        ' ... or e.g.:
                        'MsgBox "Something went wrong. Add more cases.", vbCritical
                End Select
            End If
        
        Next i

    End With

End Sub

uj5u.com熱心網友回復：

看起來您錯過了 ELSE 陳述句和 END IF。

If po.Range("M4")   2 > po.Range("O6") Then 
    po.PageSetup.PrintArea = "$B$1:$L$4"
Else
    Exit for
End if

uj5u.com熱心網友回復：

您還可以使用 GOTO 讓您的程式從代碼中的另一個位置繼續。EXIT FOR 將始終使您的 FOR 回圈結束。

If po.Range("M4")   2 > po.Range("O6") Then 
    po.PageSetup.PrintArea = "$B$1:$L$4"
Else
    GOTO ContinueHere 
End if
' Code you want to skip

ContinueHere:
' Continues to code here  

NEXT

標籤：擅长vba

上一篇：是否有任何特定功能可以找到比較兩張紙的唯一TP編號？

下一篇：如何抓取使用框架的舊學校網站