With monday.comâ€™s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.
Sub CalcQty()
Dim LR As Long, LC As Integer, i As Integer, iLvl As Long
Dim iMult(52) As Double
Dim r As Range
' find last row and last column
With ActiveSheet.Range("A:ZZ")
Set r = .Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByRows, LookIn:=xlFormulas)
End With
LR = r.Row
With ActiveSheet.Range("A:ZZ")
Set r = .Find(What:="*", After:=.Cells(1, 1), SearchDirection:=xlPrevious, SearchOrder:=xlByColumns, LookIn:=xlFormulas)
End With
LC = r.Column
' Calculate the needed Quantities
For i = 1 To LR
For iLvl = 1 To LC
If (Cells(i, iLvl).Value <> "") Then
Exit For
End If
Next iLvl
If (iLvl = 1) Then
iMult(1) = Sheets("BomQty").Cells(i, 2).Value
Else
iMult(iLvl) = iMult(iLvl - 1) * Sheets("BomQty").Cells(i, 2).Value
End If
Sheets("CalcQty").Cells(i, 1).Value = Cells(i, iLvl).Value
Sheets("CalcQty").Cells(i, 2).Value = iLvl
Sheets("CalcQty").Cells(i, 3).Value = iMult(iLvl)
Sheets("CalcQty").Cells(i, 4).Value = Sheets("BomQty").Cells(i, 3).Value
Next i
End Sub
Final
Semi
Semi
Raw
Raw
Semi
Raw
And this, for instance. (and there are other possible permutations)
Final
Semi
Semi
Raw
Raw
Semi
Raw