How to manipulate value in Column related to multi level BOM

[dmadhup]

Hi,
I want to manipulate the value in column 1 based on the adjacent column.

Col-A Col-B
1 1
1 2
1 2
2 3
2 3
2 3
1 2
1 2

If you see last two value in col-B, this 2 report to 1 which is on the top in col-B and the corresponding value in col-A is 1. Therefore on the bottom of col-A, I need to write 1.

Any help is highly appreciated. Looking for VBA to solve this.

 

[PhilS2520]

Hi dmadhup,

OK would you please state the relationship between the numbers in Col A and the ones in Col B. It is not clear what you are trying to accomplish.

Why is the first number in Col B a 1? Why it is not a 2 like the others?!

 

[dmadhup]

Hi PhilS2520,

Actually the one is column B are predefined by the users. This is the example of Multi-Level BOM in manufacturing process. Another column is column C which value is also defined by the users as follows:

Col-A Col-B Col-C
1 1 0
1 2 1
1 2 0
2 3 1
2 3 1
2 3 1
1 2 1
1 2 1

Note: Values in Col-B and Col-C are provided. Based on provided value I am trying to achieve Col-A value. So I wrote following macro but this doesn't give me last one's on col-A.

Sub ColumnA_Value()
Dim last, i As Long
    last = Cells(Rows.Count, 3).End(xlUp).Row
    Range("A2").Value = "=IF($B$2=0,0,1)"
    
For i = 2 To last - 1
    If Range("C" & i + 1).Value = 1 Then
        Range("A" & i + 1) = Range("A" & i).Value
    ElseIf Range("C" & i + 1).Value = 0 Then
        Range("A" & i + 1) = Range("A" & i).Value + 1
    End If
Next I
End Sub

 

[dmadhup]

Any help?