excel VBA 实现多条件横向查找,找到则复制到另一个sheet,没找到则查找下一行
时间: 2023-04-02 18:03:48 浏览: 154
可以使用VBA中的For循环和If语句来实现多条件横向查找,找到后使用Copy方法将数据复制到另一个sheet中。如果没找到,则继续查找下一行。具体实现方法可以参考以下代码:
Sub MultiConditionLookup()
Dim wsSource As Worksheet
Dim wsTarget As Worksheet
Dim i As Long
Dim j As Long
Dim k As Long
Dim l As Long
Dim found As Boolean
Set wsSource = ThisWorkbook.Worksheets("Sheet1") '源数据所在的sheet
Set wsTarget = ThisWorkbook.Worksheets("Sheet2") '目标sheet
'设置查找条件
Dim criteria1 As String
Dim criteria2 As String
criteria1 = "条件1"
criteria2 = "条件2"
'设置源数据范围
Dim lastRow As Long
Dim lastCol As Long
lastRow = wsSource.Cells(Rows.Count, 1).End(xlUp).Row
lastCol = wsSource.Cells(1, Columns.Count).End(xlToLeft).Column
'循环查找
For i = 2 To lastRow '从第2行开始查找,第1行为表头
found = True '假设已找到
For j = 1 To lastCol
If wsSource.Cells(i, j) <> criteria1 And wsSource.Cells(i, j) <> criteria2 Then
found = False '如果有一个条件不符合,则认为没找到
Exit For '退出循环
End If
Next j
If found Then '如果找到
k = wsTarget.Cells(Rows.Count, 1).End(xlUp).Row + 1 '目标sheet中下一个空行
For l = 1 To lastCol
wsTarget.Cells(k, l) = wsSource.Cells(i, l) '复制数据到目标sheet
Next l
End If
Next i
End Sub