BOM表第二列为物料名称第三列为物料规格第一列为物料编码仅有表头其余空白经过匹配处理后得到的物料编码将写入此列；物料表第一列为物料编码 第二列为物料名称第三列为物料规格；BOM表和物料表的第三列物料规格字符均存在多余空格、大小写混用、星号乘号混用等不规范现象请注意字符处理；物料表内存在多行相同物料编码对应不同物料规格的数据行在代码处理过程中匹配到任何一行对应规格得到物料编码即可；如果BOM表内物料

以下是一个用VBA编写的物料匹配程序的示例：

Sub MatchMaterials()
    Dim bomSheet As Worksheet
    Dim materialSheet As Worksheet
    Dim bomLastRow As Long
    Dim materialLastRow As Long
    Dim bomCodeCol As Range
    Dim bomNameCol As Range
    Dim bomSpecCol As Range
    Dim materialCodeCol As Range
    Dim materialNameCol As Range
    Dim materialSpecCol As Range
    Dim bomCode As String
    Dim bomName As String
    Dim bomSpec As String
    Dim materialCode As String
    Dim materialName As String
    Dim materialSpec As String
    Dim i As Long
    Dim j As Long
    Dim matchFound As Boolean
    
    ' 设置BOM表和物料表的工作表
    Set bomSheet = ThisWorkbook.Worksheets("BOM表")
    Set materialSheet = ThisWorkbook.Worksheets("物料表")
    
    ' 获取BOM表和物料表的最后一行
    bomLastRow = bomSheet.Cells(bomSheet.Rows.Count, 1).End(xlUp).Row
    materialLastRow = materialSheet.Cells(materialSheet.Rows.Count, 1).End(xlUp).Row
    
    ' 设置BOM表和物料表的列范围
    Set bomCodeCol = bomSheet.Range("A2:A" & bomLastRow)
    Set bomNameCol = bomSheet.Range("B2:B" & bomLastRow)
    Set bomSpecCol = bomSheet.Range("C2:C" & bomLastRow)
    Set materialCodeCol = materialSheet.Range("A2:A" & materialLastRow)
    Set materialNameCol = materialSheet.Range("B2:B" & materialLastRow)
    Set materialSpecCol = materialSheet.Range("C2:C" & materialLastRow)
    
    ' 循环遍历BOM表的每一行
    For i = 1 To bomLastRow - 1
        bomCode = bomCodeCol.Cells(i).Value
        bomName = Trim(bomNameCol.Cells(i).Value)
        bomSpec = Trim(bomSpecCol.Cells(i).Value)
        
        ' 初始化匹配标志
        matchFound = False
        
        ' 循环遍历物料表的每一行
        For j = 1 To materialLastRow - 1
            materialCode = materialCodeCol.Cells(j).Value
            materialName = Trim(materialNameCol.Cells(j).Value)
            materialSpec = Trim(materialSpecCol.Cells(j).Value)
            
            ' 判断物料名称和规格是否完全匹配
            If UCase(materialName) = UCase(bomName) And UCase(materialSpec) = UCase(bomSpec) Then
                ' 将匹配到的物料代码写入BOM表第一列对应的行，并设置字体颜色为黑色
                bomCodeCol.Cells(i).Value = materialCode
                bomCodeCol.Cells(i).Font.Color = RGB(0, 0, 0)
                matchFound = True
                Exit For
            ElseIf UCase(materialSpec) = UCase(bomSpec) Then
                ' 将匹配到的物料代码写入BOM表第一列对应的行，并设置字体颜色为红色
                bomCodeCol.Cells(i).Value = materialCode
                bomCodeCol.Cells(i).Font.Color = RGB(255, 0, 0)
                matchFound = True
                Exit For
            End If
        Next j
        
        ' 如果没有找到完全匹配或部分匹配的物料，则将BOM表第一列对应的行清空
        If Not matchFound Then
            bomCodeCol.Cells(i).ClearContents
        End If
    Next i
End Sub

请根据实际的工作表名称和数据范围进行调整，并在VBA编辑器中将此代码添加到工作表的代码模块中。然后，您可以通过运行MatchMaterials子过程来执行物料匹配