VBA - Recursive Find

[vcb]

Hi,

I adapted that code from the Excel help example for the Find function and from a guy from a forum somewhere.

I want to transform that kind of BOM to a multi level BOM.

'This is from a guy looking for the same thing on another forum.
FG1|SA1|1
FG2|SA2|1
FG1|CO1|4
SA1|SA3|2
SA1|CO2|3
SA3|CO3|2
SA2|SA3|4
SA2|CO4|1

FG1|FG1|SA1
FG1|SA1|SA3
FG1|SA3|CO3
FG1|SA1|CO2
FG1|FG1|CO1
FG2|FG1|SA2
FG2|SA2|SA3
FG2|SA3|CO3
FG2|SA2|CO4

Function find1(item)

    Dim strFirstAddress As String
    Set c = Worksheets("Format").range("B:B").Find(item)

    If Not c Is Nothing Then
        strFirstAddress = c.Address
        Do
            'debug
            c.Select
            c.Offset(0, 1).Select
            
            'find the next assembly (calling itself)
            find1 (c.Offset(0, 1).Value)
            
            'Come back and findnext previous value
            Set c = Worksheets("Format").range("B:B").FindNext(c)
            
        Loop While c.Address <> strFirstAddress
    Else
        Debug.Print "Cells Not Found"
    End If

End Function

I'm calling it like that.

sub test()
    For Each c In range("B:B")
    c.Select
        If c.Value = 1 Then
            newSubAssy = c.Offset(0, 1).Value
            find1 (newSubAssy)
        End If
    Next c
End Sub

My problem is when it doesn't find a value within the 2nd call, returns to the previous one, c is nothing, which shouldn't be the case!!

Thanks for helping.

 

[chirp]

Hi,

it seems to me like you are doing a lot of unnecessary looping and probably dont even need to use a recursive function...

can you please just briefly explain in words what you are trying to accomplish?

 

[vcb]

Hi chirp,

Thanks for taking time to read my post.

Here an example of the report I get :

A 1
A 2
A B
A C
B 3
B 4
B D
C 9
D 0

And this is the way I'd like to present.

A includes components 1, 2
A includes sub assembly B

B includes components 3,4​

B includes sub assembly D​

D includes component 0​

A includes sub assembly C

C includes components 9​

I feel the recursive function Find is the right way to achieve it, but I get that error after the component (for example the component 4) is not found. (The previous

.findnext(c)

returns nothing.

I would be great if you have another suggestion to find and rebuild the structure.

Thanks