-1

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

Gabriel Jalbert
  • 11
  • 4
  • 5
    *I would like to create a VBA macro* - Please read [Why is "Can Someone Help Me" not an actual question](https://meta.stackoverflow.com/questions/284236/why-is-can-someone-help-me-not-an-actual-question) – Scott Holtzman Sep 27 '17 at 15:24
  • What advantage are you expecting to gain by doing in in VBA vs a formula that is already working? – braX Sep 27 '17 at 15:25
  • 1
    @braX 58 The excel file is generated by another software, and I don't want to have to ask the other users to copy/paste the data to a template file then risk them tempering with the formula (which are complex because of the numbering system) the macro would ensure that no error could be made. The parent are afterward input into our erp system. – Gabriel Jalbert Sep 27 '17 at 15:34
  • Can we see a copy of the working formula that you are using? It may help show exactly what method you are using to extract the parent number. – K.Dᴀᴠɪs Sep 27 '17 at 15:41
  • this should help point you in the right direction: https://stackoverflow.com/questions/18617349/excel-last-character-string-match-in-a-string – SeanC Sep 27 '17 at 15:44
  • @SeanC I've updated my question to include the formula. – Gabriel Jalbert Sep 27 '17 at 15:59

1 Answers1

0

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.

R. Roe
  • 609
  • 7
  • 18
  • @R-roe Thanks, that worked great. I changed however your : stringToFind = Left(cell, level - 3 + level) to be stringToFind = Left(cell, InStrRev(cell, ".") - 1) since the original one didn't work well with item numbers like "1.10.4.1" as it would try to find the string "1.10." as the parent. – Gabriel Jalbert Oct 06 '17 at 15:51