Excel VBA find part number from indented BOM [closed]
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?
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.