我公司需要对已有符合条件的物料进行整理,选出符合条件的物料,供技术与采购部门使用,我在PLM 系统中进行了简单的查询,主要是代号以1或0开头的物料,查询出800条记录,在这800条记录中进行详细的筛选规则如下:
前六位编码数值:
第一段 | 第二段 | 第三段 | 第四段 | 第五段 |
0 | 0 | 0 | 05 | 3 |
1 | 1 | 1 | 07 | 4 |
2 | 2 | 11 | ||
3 | 3 | 12 | ||
4 | 4 | 13 | ||
14 | ||||
15 | ||||
45 | ||||
70 | ||||
71 | ||||
72 | ||||
73 | ||||
74 | ||||
90 |
将以上数值组合的结果共1400种,vba程序如下:嵌套循环
Public Sub pailie()
Dim i, b, c, d, e, f As Integer
i = 1
For b = 3 To 4
For c = 3 To 7
For d = 3 To 7
For e = 3 To 16
For f = 3 To 4
Cells(i, 13) = Cells(b, 2) & Cells(c, 3) & Cells(d, 4) & Cells(e, 5) & Cells(f, 6)
i = i + 1
Next
Next
Next
Next
Next
End Sub
在已有的800条记录中进行查询,编码的前六位有符合任意一种组合结果时,将此记录复制到一个单独的表中,程序如下:
Sub 查询()
Dim i, j, k, z, m, n As Integer
Dim wk As Workbook
Set wk = Application.Workbooks.Open("C:\Users\Administrator\Desktop\新建文件夹\结果.xlsx ")
k = 1
z = 1
For i = 2 To 1401
For j = 2 To 6023
If Cells(i, 1) = Left(Cells(j, 2), 6) Then
For z = 2 To 55
wk.Worksheets("sheet1").Cells(k, 1).Value = Cells(i, 1)
wk.Worksheets("sheet1").Cells(k, z).Value = Cells(j, z)
Next
k = k + 1
Else
z = 1
End If
Next
Next
End Sub
完成!