Solved: Change value of one cell based on another cell (Tree logic)

[kishlaya]

Dear Friends
i have a sheet named sheet1 which has some data arrange in an order according to the number in L column. if the value at column L is 1 then it is the highest node of the tree, if it is 2 then the next and so on. the tree can be viewed by clicking the button Tree on sheet1.
The last number at each node of the tree represents the quantity of that particular node which is taken from the column "I" of Sheet 1. now there are many values(or nodes) which will appear more than two times in the tree. i want to copy all the data into a new sheet with single entry of each node with all the quantities added up.

but the trickiest point is that suppose for a node its parent has quantity 2 then that particular node's quanity has to be multiplied with the quantity of its parent 2 times and then should be added up with the other occurences to generate the single entry.

I am stuck up at this point for over a week now. :banghead: For any clarifications please contact.
Kindly help me
Thanks in advance
PS: I have attached a sample sheet for your reference.

[Benzadeus]

Lots of data!

Did you try Me.tvParts.Nodes(number).Parent. to get Parent's text, or key, or whatever?

[kishlaya]

i dont want to get the parent's key or text, i want the quantity of each parent should be multiplied to its child nodes so that i can get the correct number of quantity.

[kishlaya]

18 views but only on reply!! somebody please help, it has become very critcal for me.:bug:

[Benzadeus]

So: the row 8, where the quantity is 11, in the TreeView, would have the value 2 * 11 = 22 assigned?

[kishlaya]

Yes you are right!
Any clues how to do it?

[Benzadeus]

I see you are creating the nodes' text with concatened cells in column N, for example N2:
=+D2&$M$3&G2&$M$3&H2&$M$3&I2&$M$3&J2&$M$3

It's better you remove the quantity information from that cell and add it on runtime in VBA, for example:
Instead using:
.Add Key:=mpKey, Text:=CStr(bom.Range("N2").Value)


Use:
.Add Key:=mpKey, Text:=CStr(bom.Range("N2").Value & lngQty)

and you could calculate this lngQty calling the node's and its parents' Qty value.

[kishlaya]

ok, i am trying to understand your line of code. can you plz tell how do i calculate this lnQty?
moreover i want my unique rows (with added quantity) in a new sheet and not on the tree, can we do it like the way you are suggesting?

[Benzadeus]

Sub GenerateQtd()
Dim _
rLast As Long, _
n As Long, _
aMult(10) As Variant, _
a As Long, _
x As Long

Const cQtd As String = "I" '<=== change to suit
Const cLvl As String = "L" '<=== change to suit
Const cTotals As String = "J" '<=== change to suit
Const rFirst As Long = 2 '<=== first row; change to suit

rLast = Cells(ActiveSheet.Rows.Count, cQtd).End(xlUp).Row

Columns(cTotals).ClearContents

aMult(0) = 1
For n = rFirst To rLast
Select Case Cells(n, cLvl)
Case Cells(n + 1, cLvl) - 1
a = a + 1
aMult(a) = Cells(n, cQtd)
Case Cells(n + 1, cLvl)
Case Else
For x = 1 To Cells(n, cLvl) - Cells(n + 1, cLvl)
aMult(a) = 0
a = a - 1
Next x
End Select
Cells(n, cTotals) = Cells(n, cQtd)
For x = a To 1 Step -1
Cells(n, cTotals) = Cells(n, cTotals) * aMult(x)
Next x
Next n
End Sub

[kishlaya]

Dear Benzadeus
Thanl you very much for your code.
I tried running your code but it is giving me Runtime error 13 type mismatch error!
i also changed the values of the columns , and also it is not printing the value in column L.
Do i need to do anything else other than that?
Regards

[kishlaya]

Hey i got it working except for one little problem. the code multiplies all the quantities of the child with parent node, but it is multiplying the parent with parent also.

the row 8, where the quantity is 11, it is coming as 22, thats absolutely right but also it is multiplying the quantity of row 5 with itself making it to 4 instead of 2, the parent's quantity should not be multiplied with itself. i think a lil change in the code can do it.
Thanks in advance. :bow:

[Benzadeus]

Change
For x = a To 1 Step -1


for:
For x = Cells(n, cLvl) - 1 To 1 Step -1

[kishlaya]

Dear Benzadeus
now that problem is solved another one cropped up:(. at some places it is multiplying the quantity with zero and some places it is also not at all multiplying, any further change in the code to be done?
Regards

[kishlaya]

Dear Benzadeus
For your reference i have attached the file you can see at the row number 15,34 etc the quantity is coming as zero, please have a look at it.

[kishlaya]

nobody else is interested i guess?? can't bug benzadeus anymore, thanks for your help man, prob still unsloved though.

[kishlaya]

nobody else is interested i guess?? can't bug benzadeus anymore, thanks for your help man, prob still unsloved though.

[mdmackillop]

I suggest you post this as a new question with the revised code. You can add a link back to this for background.

[Benzadeus]

I'm sorry, getting really hard on my work. I'm not having enough spare time =\