# Excel VBA find part number from indented BOM [closed]

We have a software (Solidworks) from wich we extract a Bill of Materials in an Excel spreadsheet.
It returns the following data:

I would like to create a VBA macro that populates column C (parent) with the parent part number. For exemple cell C6 would display : 101-07798-111.

I managed to do it with an Excel formula directly in the worksheet, however I would like to do it with a VBA macro.

The excel formula requires 2 columns. "Column D" in wich I do a concatenate of a letter and the data of "column A". "Column E" wich does an Index(match) search of "column A" data to return the value of "Column B".

Column D formula : =CONCATENATE("A";A3) *without this step the main formula have errors

Column E formula : =INDEX($B$1:$B$250;MATCH((IFERROR(LEFT(D3; FIND("$"; SUBSTITUTE(D3; "."; "$"; LEN(D3)-LEN(SUBSTITUTE(D3; "."; ""))))-1);"-"));$D$1:$D$250;0))

I found ways to have a VBA script populate the rows with the formula; however since the formula contain a lot of " it causes error in the script.

What could be the best way to use the data in "column a" to get the value of "column B" in a vba script?

Thank you

## Answers:

I figured what the heck I want to figure this out so this is how I would do it.

```
Dim splitVariable As Variant
Dim level As Integer
Dim stringToFind As String
For Each cell In Range("A1:A" & [A1].End(xlDown).Row)
splitVariable = Split(cell.Value, ".") 'split the cell on the period to find the level
level = UBound(splitVariable) + 1 'add one because array is 0 indexed
If level > 1 Then 'don't do anything for the first level
stringToFind = Left(cell, level - 3 + level) 'get the level of the parent
For Each parentCell In Range("A1:A" & [A1].End(xlDown).Row) 'loop through rang again to find level
If parentCell.Value = stringToFind Then 'when the parent is found then paste it to column C
cell.Offset(0, 2) = parentCell.Offset(0, 1)
Exit For
End If
Next
End If
Next
```

don't know if that helps at all.