|
楼主 |
发表于 2020-7-21 15:20
|
显示全部楼层
看来这次没有高手帮忙了,弄了一天,总算写出来了,也不复杂,供大家参考。
Sub 物料编码检索()
Application.ScreenUpdating = False
Dim arrwl()
Dim arrwlbm2()
Dim num1%, c1$, c2$, c3$, c4$, c5$, hs%
num1 = Sheet2.[a100000].End(3).Row
ReDim arrwl(1 To num1, 1 To 3)
ReDim arrwlbm2(1 To 500)
Sheet1.Range("a5:a500").ClearContents
c1 = Worksheets("检索").Range("B4"): c2 = Worksheets("检索").Range("C4"): c3 = Worksheets("检索").Range("D4"): c4 = Worksheets("检索").Range("E4"): c5 = Worksheets("检索").Range("F4")
With Sheet2
For i = 1 To num1
arrwl(i, 1) = Worksheets("Sheet1").Cells(i, "A")
arrwl(i, 2) = Worksheets("Sheet1").Cells(i, "I")
arrwl(i, 3) = Worksheets("Sheet1").Cells(i, "J")
If c2 <> "" Then
If ((InStr(1, arrwl(i, 2), c1, 1) > 0) Or (InStr(1, arrwl(i, 2), c2, 1) > 0)) Then '不区分大小写格式 Instr(1,"pPf**f","PP",1) 结果返回1
If ((InStr(1, arrwl(i, 3), c3, 1) > 0) And (InStr(1, arrwl(i, 3), c4, 1) > 0) And (InStr(1, arrwl(i, 3), c5, 1) > 0)) Then
hs = hs + 1
arrwlbm2(hs) = arrwl(i, 1)
End If
End If
ElseIf (InStr(1, arrwl(i, 2), c1, 1) > 0) Then
If ((InStr(1, arrwl(i, 3), c3, 1) > 0) And (InStr(1, arrwl(i, 3), c4, 1) > 0) And (InStr(1, arrwl(i, 3), c5, 1) > 0)) Then
hs = hs + 1
arrwlbm2(hs) = arrwl(i, 1)
End If
End If
Next i
End With
For k = 1 To hs
Sheet1.Cells(4 + k, 1) = arrwlbm2(k)
Next k
Application.ScreenUpdating = True
End Sub |
|