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:
BOM Export result

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.