[Regulator]

Zack, that blank row can be removed. If there is no match, I would like to get a message that says no match.

[Zack Barresse]

You want a message for each? Or you want one stating which part numbers didn't match?

[Regulator]

Which ever is easier. I just would like some sort of message that makes it clear that there was no result because there was no match, not because of a formatting or formula issue.

[Zack Barresse]

So, we can make a change event, that runs every time you make a change to column B's data (starting at row 14 and going down). This will lookup the data and populate that row of info. What about the columns that you haven't identified? I.e. PT, PS, MB and UM?

[Regulator]

All I'm concerned about is the unit price.

[Zack Barresse]

Okay, try this, right click your worksheet tab (BOM1) and select View Code, paste this in there...

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    Dim ws As Worksheet, rLook As Range, rFind As Range
    Const StartRow As Long = 14

    If Target.Column <> 2 Then Exit Sub
    If Target.Row < StartRow Then Exit Sub
    Set ws = ThisWorkbook.Sheets("Indentured BOM")
    Set rLook = ws.Columns(1)
    Set rFind = rLook.Find(What:=Target.Value, LookAt:=xlWhole, MatchCase:=True)
    If rFind Is Nothing Then
        Target.Offset(0, 9).Value = ""
    Else
        Target.Offset(0, 9).Value = rFind.Offset(0, 8).Value
    End If
    
End Sub

I think it should get you what you want.

HTH

[Regulator]

It works! Perfect! Just one more thing, can you extend the ranges to the end of the sheet?

[Regulator]

Okay, I lied here is the last thing, I have more than 1 BOM sheet. For illustration purposes I only put BOM1, in reality there's BOM2, BOM3, BOM4 etc...how can I modify the macro to account for this?

[Zack Barresse]

Didn't I ask if this was the spreadsheet you were working with? This is what happens when you don't show your actual work. I didn't design a solution for your actual work, which means I'm doing twice the work now. And if I seem a little pissy it's cause I am. You have no idea how frustrating it is to do all of your work TWICE!

Okay, with that out of the way (thank you for letting me vent), please, please, please, post anything else about your spreadsheet we need to know about.

[Regulator]

I apologize, I messed up badly on this. Thank you for all your help and work. If you would be so kind to give me a second chance, I have attached the final workbook that is the actual template that I work from. Thanks again for your patience!

[Zack Barresse]

It's not that I won't give a second chance, I just would like you to understand the frustration of doing the work twice. No worries really, I've gotta jet right now for class, but I can take a look at it later.

[Regulator]

Zack, I copied and pasted this code into the individual BOMs and it works great! I'm going to count this thread as SOLVED! Thanks again for your help!

[Zack Barresse]

While that will work, it makes for a PITA, especially if you have to change any of the code. You're better off using one piece of code. You can do this by putting it in the ThisWorkbook module and making it work for all sheets...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rFind As Range
    Const StartRow As Long = 14
    If Left(Sh.Name, 3) <> "BOM" Then Exit Sub
    If Target.Column <> 2 Then Exit Sub
    If Target.Row < StartRow Then Exit Sub
    Set rFind = Sh.Columns(1).Find(What:=Target.Value, LookAt:=xlWhole, MatchCase:=True)
    If rFind Is Nothing Then
        Target.Offset(0, 9).Value = ""
    Else
        Target.Offset(0, 9).Value = rFind.Offset(0, 8).Value
    End If
End Sub

This code is UNTESTED by myself. Save your work before trying it out. You wouldn't need any of the other code in any of the other worksheets if this works for you, just this in your ThisWorkbook module.

HTH