时间戳

首页 > 解决方案 > With 语句中的 If 条件

excel - With 语句中的 If 条件

问题描述

在下面的代码中，我先过滤“PENDING：”，然后分别过滤“USA”上的第 10 列和第 2 列。所有过滤后的数据都被复制。但现在我想过滤“CANADA”和“USA”，但想要仅当第 10 列中的单元格 =“美国”且第 2 列中的单元格 =“美国”或第 10 列中的单元格 =“加拿大”且第 2 列中的单元格 =“加拿大”时才复制我知道如何过滤“美国”和“加拿大”。我想知道在代码中在哪里添加 If 条件。

ub SS()

    Dim sourceWorkbook As Workbook
    Dim targetWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim targetSheet As Worksheet

    Dim sourceWorkbookPath As String
    Dim targetWorkbookPath As String
    Dim lastRow As Long
    Dim lRow As Long, lCol As Long
    Dim LastCol As String




    ' Define workbooks paths
    sourceWorkbookPath = "Path 1"
    targetWorkbookPath = "Path 2"

    ' Set a reference to the target Workbook and sheets
    Set sourceWorkbook = Workbooks.Open(sourceWorkbookPath)
    Set targetWorkbook = Workbooks.Open(targetWorkbookPath)

    ' definr worksheet's names for each workbook
    Set sourceSheet = sourceWorkbook.Worksheets("WEXOS")
    Set targetSheet = targetWorkbook.Worksheets("Sheet1")



    With sourceSheet


        Row = .Range("J" & .Rows.Count).End(xlUp).Row

        '~~> Find the empty column after the last column
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1

        '~~> Get the column Name
        LastCol = Split(Cells(, lCol).Address, "$")(1)

        '~~> Insert the formula
        .Range(LastCol & "2:" & LastCol & lRow).Formula = "=IF(N2=""PENDING"",IF(OR(B2&J2=""USAUSA"",B2&J2=""CANADACANADA""),TRUE,FALSE),FALSE)"

        '~~> Remove existing filter
        .AutoFilterMode = False


        ' Get last row
         lastRow = .Range("J" & .Rows.Count).End(xlUp).Row

    With .Range("A1:" & LastCol & lRow)

          .AutoFilter Field:=lCol, Criteria1:="=TRUE"

        '.Range("A1:Q1").AutoFilter Field:=14, Criteria1:="PENDING"
        '.Range("A1:Q1").AutoFilter Field:=10, Criteria1:="USA", Operator:=xlOr, Criteria2:="CANADA"
        '.Range("A1:Q1").AutoFilter Field:=2, Criteria1:="USA", Operator:=xlOr, Criteria2:="CANADA"

    End With

        .Range("K2:K" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("G2")
        .Range("C2:C" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("A2")
        .Range("E2:E" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("B2")
        .Range("G2:G" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("C2")
        .Range("I2:I" & lastRow).SpecialCells(xlCellTypeVisible).Copy _
                                     Destination:=targetSheet.Range("F2")
        .Columns(lCol).Delete


    End With

    'Rest of the Code


    On Error Resume Next
    sourceSheet.ShowAllData
    On Error GoTo 0

End Sub

标签： excelvba

解决方案

这是你正在尝试的吗？

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim lRow As Long, lCol As Long
    Dim LastCol As String

    '~~> Change this to the relevant sheet
    Set ws = Sheet1

    With ws
        '~~> Find the last row in col J
        lRow = .Range("J" & .Rows.Count).End(xlUp).Row

        '~~> Find the empty column after the last column
        lCol = .Cells(1, Columns.Count).End(xlToLeft).Column + 1

        '~~> Get the column Name
        LastCol = Split(Cells(, lCol).Address, "$")(1)

        '~~> Insert the formula
        .Range(LastCol & "2:" & LastCol & lRow).Formula = "=B2=J2"

        '~~> Remove existing filter
        .AutoFilterMode = False

        '~~> Work with the relevant range
        With .Range("A1:" & LastCol & lRow)
           .AutoFilter Field:=14, Criteria1:="=PENDING"
           .AutoFilter Field:=10, Criteria1:="=Canada", Operator:=xlOr, Criteria2:="=USA"
           .AutoFilter Field:=2, Criteria1:="=Canada", Operator:=xlOr, Criteria2:="=USA"
           .AutoFilter Field:=lCol, Criteria1:="=TRUE"
        End With

        '
        '~~> Rest of your copy code
        '

        '~~> In the end delete the column after you finished copying
        .Columns(lCol).Delete
    End With
End Sub

推荐阅读