我有以下代码：
lr = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
For a = 1 To lr
If (Sheet1.Cells(a, 1) = Date Or Date - 1) And (Sheet1.Cells(a, 2) = "AA" Or Sheet1.Cells(a, 2) = "BB" Or Sheet1.Cells(a, 2) = "CC") And Sheet1.Cells(a, 3) = array(0) Then
Call ActivateSheet
Sheet1.Range(Cells(a, 4), Cells(a, 10)).Copy
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
End If
Next a
我有两列包含数据。在A列中，我们有DATE的值（我将其称为currentDate）和DATE-1的值（将其称为yesterDate）。在B列中，我可以具有三个不同的值，即AA，BB和CC。我上面的if语句（我为代码看起来不太合理而道歉，我仍在尝试学习VBA。; P）基本上检查A列中的值是currentDate还是yesterDate，并检查B列中的值是AA，BB或CC。然后，如果列A和列B的值是给定值的任意组合，它将复制该单元格范围并将其粘贴到Sheet2上。
所以这就是我想要发生的事情。从给定值的所有可能组合中，有一个我不想复制的组合，而该组合是yesterDate && CC。
我只希望我的代码复制yesterDate && AA，yesterDate && BB和currentDate && CC。根据用户输入，所有其他组合（例如currentDate && AA或currentDate && BB）都不太可能发生。
我只想排除yesterDate && CC由我的代码复制。关于如何实现此目标的任何想法？
在这里，我给你一个。试试这个。
Public Sub checkAndCopy()
Dim rowCount, row As Integer
Dim dateCell, valueCell, combinationCell As String
Dim isValid As Boolean
'Getting row count from Sheet1
rowCount = Sheet1.Cells(Rows.Count, 1).End(xlUp).row
'Looping all row from Sheet1.
For row = 1 To rowCount
'getting cell values
dateCell = Sheet1.Range("A" & row)
valueCell = Sheet1.Range("B" & row)
combinationCell = Sheet1.Range("C" & row)
'Sometime one of these cell should be blank or wrong date value.
'So, I added checking to avoid it.
'If these two cell are not empty, check date is valid or not.
If dateCell <> "" And valueCell <> "" Then
'If date value is valid, go on checking for copy cell.
If IsDate(dateCell) Then
'Reset isValid flag.
isValid = True
'You just want to exclude yesterday & CC.
'So, I only check for it.
If dateCell = Date - 1 And valueCell = "CC" Then
isValid = False
End If
'If both cell values are valid and also combination cell is valid, copy and paste cell.
If isValid And combinationCell = array(0) Then
'Select cells
Sheet1.Range(Cells(row, 4), Cells(row, 10)).Select
'Copy cells
Selection.Copy
'Paste cells
Sheet2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial
'Reset clipboard
Application.CutCopyMode = False
End If
End If
End If
Next row
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句