作者热门文章
- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
这就是我正在尝试使用当前代码 执行的操作
这是我的 Excel https://drive.google.com/file/d/0B1GLuBx-ROnhRExUM2xVbG1WOTQ/edit?usp=sharing
循环检查零件级别是否表明它是父装配体(该级别低于下面的级别)。然后,它将其下面的所有子零件/装配体的单位重量相加(子零件下面没有其他零件)。我没有太多的编程经验,所以我不确定为什么这不起作用,或者它是否是最好的方法。
Sub UpdateUnitWeight()
Const StartRow = 2
Dim oRng As Range ' Range to work on
Dim oRngSP As Range ' Range for SumProduct
Dim lRows As Long ' Counter
' Start from row "StartRow"
Set oRng = ThisWorkbook.Worksheets("Sheet1").Cells(StartRow, "A")
'Set oRng = ThisWorkbook.Worksheets("Sheet1").Range("A3")
' Loop on numeric cells from StartRow
Do Until IsEmpty(oRng) Or Not IsNumeric(oRng)
lRows = 0 ' Extra Rows belonging to current level
' find how many rows while value on row beneath greater than current
Set oRngSP = oRng.Offset(lRows + 1, 0)
Do While oRng.Value < oRngSP.Value And IsNumeric(oRngSP)
lRows = lRows + 1
Set oRngSP = oRngSP.Offset(1, 0)
Loop
Set oRngSP = Nothing
' Setup the range for SumProduct
With Range(oRng, oRng.Offset(lRows, 0)).Offset(0, 2) ' Qty column
If oRng.Value = oRngSP.Value - 1 Then
oRng.Offset(0, 5).Formula = "=sumproduct(" & Replace(.Address, "$", "") & "," & Replace(.Offset(0, 1).Address, "$", "") & ")"
oRng.Offset(0, 5).Interior.ColorIndex = 15
End If
End With
Debug.Print oRng.Offset(0, 5).Address & vbTab & oRng.Offset(0, 5).Formula
' Move the range to next row
Set oRng = oRng.Offset(1, 0)
Loop
Set oRng = Nothing
结束子
最佳答案
据我了解,您需要一个列来存储高于当前级别的 SumProduct 级别。由于样本中存在级别 1 的 UnitWeight(第 5 行)。
Sub UpdateUnitWeight()
Const StartRow = 2
Dim oRng As Range ' Range to work on
Dim oRngSP As Range ' Range for SumProduct
Dim lRows As Long ' Counter
' Start from row "StartRow"
Set oRng = ThisWorkbook.Worksheets("Sheet1").Cells(StartRow, "A")
' Loop on numeric cells from StartRow
Do Until IsEmpty(oRng) Or Not IsNumeric(oRng)
lRows = 0 ' Extra Rows belonging to current level
' find how many rows while value on row beneath greater than current
Set oRngSP = oRng.Offset(lRows + 1, 0)
Do While oRng.Value < oRngSP.Value And IsNumeric(oRngSP)
lRows = lRows + 1
Set oRngSP = oRngSP.Offset(1, 0)
Loop
Set oRngSP = Nothing
' Setup the range for SumProduct
With Range(oRng, oRng.Offset(lRows, 0)).Offset(0, 2) ' Qty column
oRng.Offset(0, 5).Formula = "=sumproduct(" & Replace(.Address, "$", "") & "," & Replace(.Offset(0, 1).Address, "$", "") & ")"
End With
Debug.Print oRng.Offset(0, 5).Address & vbTab & oRng.Offset(0, 5).Formula
' Move the range to next row
Set oRng = oRng.Offset(1, 0)
Loop
Set oRng = Nothing
End Sub
基于您的示例输出:
“UpdateUnitWeight”列的公式:
$F$2 =SUMPRODUCT(C2:C4,D2:D4)
$F$3 =SUMPRODUCT(C3,D3)
$F$4 =SUMPRODUCT(C4,D4)
$F$5 =SUMPRODUCT(C5,D5)
$F$6 =SUMPRODUCT(C6:C7,D6:D7)
$F$7 =SUMPRODUCT(C7,D7)
$F$8 =SUMPRODUCT(C8:C11,D8:D11)
$F$9 =SUMPRODUCT(C9,D9)
$F$10 =SUMPRODUCT(C10:C11,D10:D11)
$F$11 =SUMPRODUCT(C11,D11)
如果这不是正确的逻辑,您的意思是如果下面有更高的级别,您会对乘积求和直到级别下降?
<小时/> 解决方案既然目标已经明确,那么方法与上面略有不同。还建议您将宏存储在模块中,而不是该工作表对象上。
Sub UpdateUnitWeight() ' Solution
Const StartRow = 2
Dim oRng As Range ' Range to work on
Dim oRngTmp As Range ' Temporary Range for checking
Dim sTxt As String ' Temporary string for formula use
' Start from row "StartRow"
Set oRng = ThisWorkbook.Worksheets("Sheet1").Cells(StartRow, "A")
' Process all numeric cells from StartRow
Do Until IsEmpty(oRng) Or Not IsNumeric(oRng)
' Total Weight = Qty * Unit Weight (Level independent, applies to each row)
oRng.Offset(0, 4).FormulaR1C1 = "=rc[-2]*rc[-1]"
' Unit Weight may depend on levels beneath
' Current level should SUM rows below that equals (current level + 1) until same level is met
sTxt = ""
Set oRngTmp = oRng.Offset(lRows + 1, 0)
Do While Not IsEmpty(oRngTmp) And IsNumeric(oRngTmp)
Select Case oRngTmp.Value - oRng.Value
Case 1 ' if test range equals current level + 1, prepare formula (Sum of Total Weight of child)
sTxt = sTxt & "+" & Replace(oRngTmp.Offset(0, 4).Address, "$", "")
Case 0
Exit Do
End Select
Set oRngTmp = oRngTmp.Offset(1, 0)
Loop
Set oRngTmp = Nothing
' Write the formula into the Unit Weight
If Len(sTxt) > 0 Then oRng.Offset(0, 3).Formula = "=" & sTxt
' Move the range to next row
Set oRng = oRng.Offset(1, 0)
Loop
Set oRng = Nothing
End Sub
示例输出:
关于vba - 需要帮助求和单位重量和多级零件 list ,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/23328522/
我有一个 Tizen Edje 文件,它定义了我的布局。其中一个部分是一张图像,其部分名称为“警告”。该项目在边缘文件中设置为可见,并且按预期显示。 我想使用 C 代码隐藏这部分: Evas_Obje
我正在开发一个 Eclipse RPC 项目。我需要保存和恢复所有 View 的所有内容。我没有在我的项目中使用 EMF,因此我无法使用所有 EMF API。 现在我可以使用的是IMemento。 I
我是一名优秀的程序员,十分优秀!