Hi all,
I am an engineer and recently I am faced with a task to keep track of the weight of our prototype during integration. The whole platform consist of multiple assemblies and sub-assemblies. My intention is to have an Excel sheet indicating that the weight of all child components are consolidated under the parent, if the weight of a higher assembly has already been inputted.
The following are some screenshots of the if the Excel sheet is working as intended.
(1 of 3) How the sheet will appear when no recording has been done.
(2 of 3) Assuming the highest level parent has the weight recorded, all subsequent lowel levels should indicate "Weight consolidated under parent" as shown
(3 of 3) If only the sub-assembly have the weight recorded, then only lowel levels of that sub-assembly should indicate "Weight consolidated under parent" as shown
I tried to achieve the above by formulas but hit a wall due to the multi-level nature of my list. In the end, I came to a conclusion that writing a VBA program is the only way. To try to achieve the above, I read up on VBA on forums and online tutorials and even borrowed a book before giving a shot on coding my first ever VBA program. However, it did not work as I intended.
Therefore, I hope the kind folks in this forum can assist in pointing me to the right direction. Below, I have copied the code of my VBA program for your reference. I understand that it will most likely be riddled with multiple programming errors. However, I still seek your patience and kind understanding in guiding me through this learning process. Thank you!
I am an engineer and recently I am faced with a task to keep track of the weight of our prototype during integration. The whole platform consist of multiple assemblies and sub-assemblies. My intention is to have an Excel sheet indicating that the weight of all child components are consolidated under the parent, if the weight of a higher assembly has already been inputted.
The following are some screenshots of the if the Excel sheet is working as intended.
(1 of 3) How the sheet will appear when no recording has been done.
(2 of 3) Assuming the highest level parent has the weight recorded, all subsequent lowel levels should indicate "Weight consolidated under parent" as shown
(3 of 3) If only the sub-assembly have the weight recorded, then only lowel levels of that sub-assembly should indicate "Weight consolidated under parent" as shown
I tried to achieve the above by formulas but hit a wall due to the multi-level nature of my list. In the end, I came to a conclusion that writing a VBA program is the only way. To try to achieve the above, I read up on VBA on forums and online tutorials and even borrowed a book before giving a shot on coding my first ever VBA program. However, it did not work as I intended.
Therefore, I hope the kind folks in this forum can assist in pointing me to the right direction. Below, I have copied the code of my VBA program for your reference. I understand that it will most likely be riddled with multiple programming errors. However, I still seek your patience and kind understanding in guiding me through this learning process. Thank you!
VBA Code:
Private Sub Weight_Update(ByVal Target As Range)
' The variable KeyCells contains the cells that will
' cause a sheet update when they are changed.
Dim KeyCells As Range
' The variable Compare_Address is the cell for which
' other comparison variables will take reference from
Dim Compare_Address As Range
' The variable Input_Level is the level of the row
' item for which the weight has been imputted
Dim Input_Level
' The variable Compare_Level is the level of the subsequent
' row items which will be compared to Input_Level
Dim Compare_Level
' The variable Weight_Status defines whether the weight of the
' line item is consolidated by the parent
Dim Weight_Status
Set KeyCells = Range("D:D")
Set Compare_Address = Range(Target.Address)
Input_Level = Offset(Target.Address, 0, -5)
Compare_Level = Offset(Compare_Address, 1, -5)
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
Is Nothing Then
Do While True
If Compare_Level > Input_Level Then
Offset(Compare_Address, 1, 1) = "Weight consolidated under parent"
Set Compare_Address = Range(Offset(Compare_Address, 1, 0))
Loop
End Sub