Populating worksheet B from Worksheet A - VBA help

[bos1234]

Hi everyone,
I have the following scenario:
I have two worksheets RAW and BOM. What I would like to do is populate the BOM sheet FROM the RAW for certain components.


For example in BOM worksheet I have VXL5-50 (highlighted in yellow). So for that component, I search sheet1--> 'connector type' column and look if that string exists. If it does, then I increment by 1 in the QTY column in the BOM worksheet

Pseudocode:
In BOM worksheet
	For each Part number
		Compare with "connector type" in "sheet1"
		If there is a match
			Increment "QTY" value by 1 in "BOM" worksheet
		else move to next row
		If row in "sheet1" is empty
			stop script

Excel file is attached at the bottom

thank you

[AlphaFrog]

Try this...

Sub Lookup_PNs()
    Dim cell As Range
    For Each cell In Sheets("BOM").Range("A:A").SpecialCells(xlCellTypeConstants)
        If Application.CountIf(Sheets("Sheet1").Range("A:A"), "*" & cell.Value & "*") Then
            cell.Offset(, 2).Value = Application.CountIf(Sheets("Sheet1").Range("A:A"), "*" & cell.Value & "*")
        End If
    Next cell
End Sub

[nilem]

Hi bos1234,
maybe formula, such as (in C18 sheet 'BOM')

=COUNTIF(Sheet1!A2:A27,"*"&A18)

[bos1234]

thanks very much guys.
@alphafrg - appreciate it. It works fine.

@nilem - thanks for the easy solution. Appreciate it!

Not sure which one to adopt. The RAW table will always be different each day/month hence am not sure if countif can be flexible. I think the vba code might solve this issue. I will experiment and see how it goes

thanks once again!

regards,
bos1234