VBA 物料匹配程序：自动匹配BOM表和物料表

{ "title": "BOM表第二列为物料名称，第三列为物料规格，第一列为物料编码，仅有表头，其余空白，经过匹配处理后得到的物料编码将写入此列；\n物料表第一列为物料编码 ，第二列为物料名称，第三列为物料规格；\nBOM表和物料表的第三列物料规格字符均存在多余空格、大小写混用、星号乘号混用等不规范现象，请注意字符处理；\n物料表内存在多行相同物料编码对应不同物料规格的数据行，在代码处理过程中匹配到任何一行对应规格得到物料编码即可；\n如果BOM表内物料名称&物料规格与物料表内物料名称&物料规格完全一致则将匹配到的物料代码用黑色字体写入BOM表第一列对应的行；\n剩余不能完全匹配的，仅匹配物料规格是否一致，将匹配到的物料代码用红色字体写入BOM表第一列对应的行；\n请用VBA编写一个表格内物料匹配的程序内容：以下是一个用VBA编写的物料匹配程序的示例：\n\nvba\nSub MatchMaterials()\n Dim bomSheet As Worksheet\n Dim materialSheet As Worksheet\n Dim bomLastRow As Long\n Dim materialLastRow As Long\n Dim bomCodeCol As Range\n Dim bomNameCol As Range\n Dim bomSpecCol As Range\n Dim materialCodeCol As Range\n Dim materialNameCol As Range\n Dim materialSpecCol As Range\n Dim bomCode As String\n Dim bomName As String\n Dim bomSpec As String\n Dim materialCode As String\n Dim materialName As String\n Dim materialSpec As String\n Dim i As Long\n Dim j As Long\n Dim matchFound As Boolean\n \n ' 设置BOM表和物料表的工作表\n Set bomSheet = ThisWorkbook.Worksheets("BOM表")\n Set materialSheet = ThisWorkbook.Worksheets("物料表")\n \n ' 获取BOM表和物料表的最后一行\n bomLastRow = bomSheet.Cells(bomSheet.Rows.Count, 1).End(xlUp).Row\n materialLastRow = materialSheet.Cells(materialSheet.Rows.Count, 1).End(xlUp).Row\n \n ' 设置BOM表和物料表的列范围\n Set bomCodeCol = bomSheet.Range("A2:A" & bomLastRow)\n Set bomNameCol = bomSheet.Range("B2:B" & bomLastRow)\n Set bomSpecCol = bomSheet.Range("C2:C" & bomLastRow)\n Set materialCodeCol = materialSheet.Range("A2:A" & materialLastRow)\n Set materialNameCol = materialSheet.Range("B2:B" & materialLastRow)\n Set materialSpecCol = materialSheet.Range("C2:C" & materialLastRow)\n \n ' 循环遍历BOM表的每一行\n For i = 1 To bomLastRow - 1\n bomCode = bomCodeCol.Cells(i).Value\n bomName = Trim(bomNameCol.Cells(i).Value)\n bomSpec = Trim(bomSpecCol.Cells(i).Value)\n \n ' 初始化匹配标志\n matchFound = False\n \n ' 循环遍历物料表的每一行\n For j = 1 To materialLastRow - 1\n materialCode = materialCodeCol.Cells(j).Value\n materialName = Trim(materialNameCol.Cells(j).Value)\n materialSpec = Trim(materialSpecCol.Cells(j).Value)\n \n ' 判断物料名称和规格是否完全匹配\n If UCase(materialName) = UCase(bomName) And UCase(materialSpec) = UCase(bomSpec) Then\n ' 将匹配到的物料代码写入BOM表第一列对应的行，并设置字体颜色为黑色\n bomCodeCol.Cells(i).Value = materialCode\n bomCodeCol.Cells(i).Font.Color = RGB(0, 0, 0)\n matchFound = True\n Exit For\n ElseIf UCase(materialSpec) = UCase(bomSpec) Then\n ' 将匹配到的物料代码写入BOM表第一列对应的行，并设置字体颜色为红色\n bomCodeCol.Cells(i).Value = materialCode\n bomCodeCol.Cells(i).Font.Color = RGB(255, 0, 0)\n matchFound = True\n Exit For\n End If\n Next j\n \n ' 如果没有找到完全匹配或部分匹配的物料，则将BOM表第一列对应的行清空\n If Not matchFound Then\n bomCodeCol.Cells(i).ClearContents\n End If\n Next i\nEnd Sub\n\n\n请根据实际的工作表名称和数据范围进行调整，并在VBA编辑器中将此代码添加到工作表的代码模块中。然后，您可以通过运行MatchMaterials子过程来执行物料匹配。