VBA macro to automate a tree-structure for Bill of Materials
VBA macro to automate a tree-structure for Bill of Materials
Need someone help
I’m working to a 12-level Production Bill of Materials Excel 2010 spreadsheet with the following data
• Level of BOM (from 0 for the end-item, to 6 in the example)
• Part number
• Description
• Quantity for system
• Unit measure (kg, meters, number of pieces, so on)
• Total cost (calculated formula, at the each level as the intermediate sum of total cost of components for the following level. For example: total cost for end-item A of level 1= the sum of the total cost of each components of A in level 2)
• Price currency (eur, dollars)
Each part number may be repeated more than one time, at different levels of the structure.
I have created the structure manually, with the data/group/create structure/group rows, as you can see in the picture.
For each item of level 2, I have a tree containing the p/n of level 3 to 6, ending to the following p/n of level 2. Then for the first item of level 3 I have another tree, containing the p/n of level 4 to 6 ending with following p/n of level 3.
With 12-levels BOMs, it becomes particular time-spending.
So I want to write a VBA macro that create the same structure automatically.
We have only data, no cells with calculated formula: so I can’t use the option Data/Group/Automated Structure that only works with a group of rows ending with a calculate-formula cell.
Eventually we can use the sum of total cost, for each level.
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
I think you are in the wrong forum. We are here to support Apache OpenOffice, LibreOffice and their relatives. Your question seems to be purely about Microsoft Office. Someone may be able to help you, but an MSOffice forum would be a better place to try.
OpenOffice 4.1 on Windows 10 and Linux Mint
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
If your question is answered, please go to your first post, select the Edit button, and add [Solved] to the beginning of the title.
Re: VBA macro to automate a tree-structure for Bill of Mater
i have found a similar problem solved. just look at here:
http://www.excelforum.com/showthread.php?t=991600
multi-level bill of material automatic grouping
if you have a BOM with 9 levels or more, because Excel 2010 only support max 8 outline, you have to modify the VBA code, as indicated in the post.
then if you want to have partial sum for each level (such as working time, cost of materials) or a complexive cost roll-up function, instead of using excel function SUMIF, you may try this
http://www.excelbanter.com/showthread.php?t=134919
wich does not require to specify for each row/level the range of cells you have to sum.
you can "automate" partial sum for each BOM-level, and the cost roll-up: for each SUMLOWERLEVEl we have tho arguments in input, the currentlevel of BOM (the row of the cell we are writing the formula) and a range of cells for sum, which starts with the following row and ends with startingcell.end(xlDown).row-->with the last row of the BOM, for any level we are summing.
1) Like SUMIF function) if any row at position 100 with BOM- Level 2 is followed by another row at position 101 with BOM level 2 or 1 (so that position 100 has no components, is a buy-item with related purchase order and supplier-price), the function return 0 and you don't have a "fork"(if ..then "sum cells at row 100"..else "VB custom funtion")--> to insert a second alternative formula (like = sum cells at position 100) when this condition is verified. You may write if(SumLOWERLEVEL(A1; I2:$I$102) = 0; cell formula; SumLOWERLEVEL(A1; I2:$I$102) )..
2) just to have the sum if some of the cells are text, blank, error of message (Excel o Calc "standard" SUM function does not do so), you may modify SUMLOWERLEVEL public function VB-code, as follows:
If IsNull(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsEmpty(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsError(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If Cells(row, colTotal).Value = "#N/D" Then
GoTo CELLA_DOPO
End If
'to test, in some case gives "error" message
If IsNumber(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsNonText(Cells(row, colTotal)) = False Then
GoTo CELLA_DOPO
End If
Sum = Sum + Cells(row, colTotal)
End If
End If
'point of reference for GoTo instruction
CELLA_DOPO:
'increment the row and loop
row = row + 1
Loop
'return total sum
SumLowerLevel = Sum
End function
'we are using some VB-worksheet function that are not provided for Calc or Excel formulas in worksheet cells
http://www.excelforum.com/showthread.php?t=991600
multi-level bill of material automatic grouping
if you have a BOM with 9 levels or more, because Excel 2010 only support max 8 outline, you have to modify the VBA code, as indicated in the post.
then if you want to have partial sum for each level (such as working time, cost of materials) or a complexive cost roll-up function, instead of using excel function SUMIF, you may try this
http://www.excelbanter.com/showthread.php?t=134919
wich does not require to specify for each row/level the range of cells you have to sum.
you can "automate" partial sum for each BOM-level, and the cost roll-up: for each SUMLOWERLEVEl we have tho arguments in input, the currentlevel of BOM (the row of the cell we are writing the formula) and a range of cells for sum, which starts with the following row and ends with startingcell.end(xlDown).row-->with the last row of the BOM, for any level we are summing.
1) Like SUMIF function) if any row at position 100 with BOM- Level 2 is followed by another row at position 101 with BOM level 2 or 1 (so that position 100 has no components, is a buy-item with related purchase order and supplier-price), the function return 0 and you don't have a "fork"(if ..then "sum cells at row 100"..else "VB custom funtion")--> to insert a second alternative formula (like = sum cells at position 100) when this condition is verified. You may write if(SumLOWERLEVEL(A1; I2:$I$102) = 0; cell formula; SumLOWERLEVEL(A1; I2:$I$102) )..
2) just to have the sum if some of the cells are text, blank, error of message (Excel o Calc "standard" SUM function does not do so), you may modify SUMLOWERLEVEL public function VB-code, as follows:
If IsNull(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsEmpty(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsError(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If Cells(row, colTotal).Value = "#N/D" Then
GoTo CELLA_DOPO
End If
'to test, in some case gives "error" message
If IsNumber(Cells(row, colTotal)) = True Then
GoTo CELLA_DOPO
End If
If IsNonText(Cells(row, colTotal)) = False Then
GoTo CELLA_DOPO
End If
Sum = Sum + Cells(row, colTotal)
End If
End If
'point of reference for GoTo instruction
CELLA_DOPO:
'increment the row and loop
row = row + 1
Loop
'return total sum
SumLowerLevel = Sum
End function
'we are using some VB-worksheet function that are not provided for Calc or Excel formulas in worksheet cells
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
again, with a minimal change to VB code (cicle do...loop with no instructions), we have a similar function that gives us for each level of the BOM (in column A), the previous row to the closest cell with the same BOM-level. Es:
BOM level p/n 'RigaFineSomma(.. ; ..)
0 p/n 1 15 =RigaFineSomma(A2;A3:$A$15), 2 is the current row
1 p/n 2 3
1 p/n 3 13
2 p/n 4 5
2 p/n 5 12
3 p/n 6 7
3 p/n 7 8
3 p/n 8 12
4 p/n 9 10
4 p/n 10 12
5 p/n 11 12
2 p/n 12 13
1 p/n 13 14
1 p/n 14 15
so for SUMIF partialsum for each level, we know the last row of the range of cells. For level 0, we have to sum from row 2 to 15; for level 1 (on row 4) we have to sum form row 5 to row 13 (on row 14, we have another level 1).
code:
BOM level p/n 'RigaFineSomma(.. ; ..)
0 p/n 1 15 =RigaFineSomma(A2;A3:$A$15), 2 is the current row
1 p/n 2 3
1 p/n 3 13
2 p/n 4 5
2 p/n 5 12
3 p/n 6 7
3 p/n 7 8
3 p/n 8 12
4 p/n 9 10
4 p/n 10 12
5 p/n 11 12
2 p/n 12 13
1 p/n 13 14
1 p/n 14 15
so for SUMIF partialsum for each level, we know the last row of the range of cells. For level 0, we have to sum from row 2 to 15; for level 1 (on row 4) we have to sum form row 5 to row 13 (on row 14, we have another level 1).
code:
Code: Select all
Public Function RigaFineSomma(level As Range, answerLoc As Range) As Currency
Application.Volatile
AllowUdfs = True
'Forces Excel to recalculate all values when a
'cell changes
'Variables
Dim row, col, CurrentLevel As Integer
Dim Sum As Double
Sum = 0
'set starting summation row and column cell locations based on input
'Levellocations
row = level.row + 1
col = level.Column
'set current row Level based on input Level value
CurrentLevel = level.Value
'set the cell to gather the sums from, should be the same and return
'Location
colTotal = answerLoc.Column
'interate over all rows below current
Do While (Cells(row, col) > CurrentLevel)
'increment the row and loop
row = row + 1
Loop
'return total sum
RigaFineSomma = row - 1
End Function
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
you may modify the User Defined Function to find the first part-number father, for each row of the Bill of Materials
example as in attachment
NEW CODE:
example as in attachment
NEW CODE:
Code: Select all
Public Function RigaFineSommaP(level As Range, answerLoc As Range) As Currency
'la funzione ritorna la riga del (primo) codice padre
'con livello = quello della riga corrente (Level) - 1
Application.ScreenUpdating = True
Application.EnableEvents = True
AllowUdfs = True
Application.Calculation = xlManual
'Forces Excel to recalcuate all values when a
'cell changes
'========================================================='
'Generates sum of all values in "answerLoc2"
'for all rows where the "Level" is one increment higher
'========================================================='
'Variables
Dim row, col, CurrentLevel As Long
Dim Sum As Double
Sum = 0
On Error GoTo CONTINUA
'set starting summation row and column cell locations based on input
'Levellocations
' seleziona le righe dal livello 0 (end-item) a quella precedente la cella attiva
row = level.row - 1
col = level.Column
'set current row Level based on input Level value
CurrentLevel = level.Value
'set the cell to gather the sums from, should be the same and return
'Location
colTotal = answerLoc.Column
'interate over all rows below current
Do While (Cells(row, col) >= CurrentLevel)
'decrementa the row and loop
row = row - 1
Loop
Application.Calculation = xlAutomatic
'return total sum
RigaFineSommaP = row
'ricalcola la funzione in tutte le celle del foglio attivo
'evita copia-valori dopo Invio su formula somma.se
'in celle di altro foglio della stessa cartella
Exit Function
CONTINUA:
End Function
- Attachments
-
- FIND BOM ITEM FATHER UDF example.xlsb
- (15.45 KiB) Downloaded 771 times
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
This forum is about a completely different software product. Nobody here runs Excel.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: VBA macro to automate a tree-structure for Bill of Mater
Thank you. I know, but some people may find useful this code to translate it into a macro for OpenOffice CalcVilleroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
Hopefully not.marocdea wrote:Thank you. I know, but some people may find useful this code to translate it into a macro for OpenOffice CalcVilleroy wrote:This forum is about a completely different software product. Nobody here runs Excel.
Remove sheet protection.
Convert column C to numbers.
H3: =LOOKUP(C3-1;$C$1:$D2)
copy down
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: VBA macro to automate a tree-structure for Bill of Mater
it works fine in the excel, in attachment.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
I don't understand. If the levels need to be text for some reason: =LOOKUP(TEXT(VALUE(C3)-1;"@");$C$1:$D2)marocdea wrote:it works fine in the excel, in attachment.
it does'nt work if you have BOM Level and part number columns given by two Excel formulas, with cell number format.
A professional solution for this type of problem (items related to other items) would use a relational database rather than a spreadsheet.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: VBA macro to automate a tree-structure for Bill of Mater
hi, last question, that may help everibody working with Bill of Material.
Is there an Excel formula to find the last child of a p/n parent?
e.g.
look at the file in attachment, we need an Excel formula that for row n. 6 where (you may read BOM Level 4, on cell C6) return the last child (so, with BOM Level 5)...
a) the row of the last child: row 14
b) the p/n of the last child: p/n 13
thank you a lot
Is there an Excel formula to find the last child of a p/n parent?
e.g.
look at the file in attachment, we need an Excel formula that for row n. 6 where (you may read BOM Level 4, on cell C6) return the last child (so, with BOM Level 5)...
a) the row of the last child: row 14
b) the p/n of the last child: p/n 13
thank you a lot
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
If your data processing relies on a distinct sort order in the stored tables, you are doomed anyway.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: VBA macro to automate a tree-structure for Bill of Mater
there is only one Excel Sheet with the BOM as in attachment. There are no stored tables, data are sorted not by name, but by their position in the Bill of Materials, as shown in the file. I think it may be a common question for many users.
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
A click on a sort button destroys everything. It is a common question for many users who try desparately to misuse a spreadsheet as a database without knowing any of them. I guess you find the last position of an item in a column with some array function such as: =max(row(column)*(item=column)) [Ctrl+Shift+Enter]
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: VBA macro to automate a tree-structure for Bill of Mater
thanks everybody.
i have created a small help-file, with a Bill of Materials of only 100 items.
But it still works for BOM of 9000 items..
there is the indented bill of materials, and three different ways to find the parent p/n for each row-items of the BOM, with the same result.
the first way, with an Array Formula, may be find in some Excel forums..the secondo with sumproduct function do the same thing..
the third working with a User Defined Function needs the file to be save in .xslm format (macro enabled..so it oes not work with .xls, .xlsx, .xlsb file extensions).
The function CERCA seems to work only in the prevous case of a BOm with one occurence for each p/n item..with no p/n repeated.
i have created a small help-file, with a Bill of Materials of only 100 items.
But it still works for BOM of 9000 items..
there is the indented bill of materials, and three different ways to find the parent p/n for each row-items of the BOM, with the same result.
the first way, with an Array Formula, may be find in some Excel forums..the secondo with sumproduct function do the same thing..
the third working with a User Defined Function needs the file to be save in .xslm format (macro enabled..so it oes not work with .xls, .xlsx, .xlsb file extensions).
The function CERCA seems to work only in the prevous case of a BOm with one occurence for each p/n item..with no p/n repeated.
- Attachments
-
- Bill of Materilas part number code example.xlsm
- (127.89 KiB) Downloaded 1194 times
openoffice 3.1 with windows 7
Re: VBA macro to automate a tree-structure for Bill of Mater
For Excel compatible solutions you need Excel and nothing but Excel alone, particularly when macros are involved.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice