Excel VBA - 如果值存在> x，加总和 [英] Excel VBA - If value exists > x, add to sum

问题描述

我有一个表的时间值。如果指定行中存在大于90分钟（1:30:00）的任何值，则需要在行末添加一个运行总计的差异（即多大）。所以，该框可能是空白的，只能有一个单元格的值，或者可以添加多个值。我已经有了For循环遍历每个单元格。我需要该部分来计算值。而且理想情况下，如果有一个嵌套循环为6行执行6个单独的总和...

 '添加break munutes 
 fp.Activate 
 Dim rng As Range 
对于每个rng In Range（B3：F3）
如果rng.Value> TimeValue（1:31:00）然后
 
结束如果
下一个rng 
  

解决方案

难以说如果这是完全准确没有更多的信息，但这样的东西应该适合你：

  Sub tgr（）
 
 Dim aTimes As Variant 
 Dim dTime As Double 
 Dim aSumResults（）As Double 
 Dim lResultIndex As Long 
 Dim i As Long，j As Long 
 Dim bFirst As Boolean 
 
 dTime = TimeValue（01:00:00）
 aTimes = ActiveSheet.Range（B3：F9）。值'更改为完整的表范围
 ReDim aSumResults（1到UBound（aTimes，1） -  LBound（aTimes，1）+ 1，1到1）
 
对于i = LBound（aTimes，1）到UBound（aTimes，1）
'每个我代表一行数据
'Go通过每一列收集条件和
 lResultIndex = lResultIndex + 1 
 bFirst = True'使用bFirst忽略大于dTime的第一个值
对于j = LBound（aTimes，2）To UBound（ aTimes，2）
如果aTimes（i，j）> dTime然后
如果bFirst然后
'如果为行找到第一个值，忽略它
 bFirst = False 
 Else 
'不是找到的第一个值，包括总和
 aSumResults（lResultIndex，1）= aSumResults（lResultIndex，1）+ aTimes（i，j） -  dTime 
 End If 
 End If 
 Next j 
下一步i 
 
'输出结果
 ActiveSheet.Range（G3）。调整大小（UBound（aSumResults，1））。Value = aSumResults 
 
 End Sub 
  

I have a table with time values. If any value exists in a specified row that is greater than 90 minutes (1:30:00), I need to add the difference (i.e. how much greater it is) to a running total at the end of the row. So, that box could be blank, could have just one cell's value, or could have multiple values added. I already have the For loop to go through each cell in a row. I need the part to sum the values. And ideally, if there was a nested loop to do 6 separate sums for the 6 rows...

'Add break munutes
fp.Activate
Dim rng As Range
For Each rng In Range("B3:F3")
    If rng.Value > TimeValue("1:31:00") Then

    End If
Next rng

解决方案

Hard to say if this is fully accurate without more information, but something like this should work for you:

Sub tgr()

    Dim aTimes As Variant
    Dim dTime As Double
    Dim aSumResults() As Double
    Dim lResultIndex As Long
    Dim i As Long, j As Long
    Dim bFirst As Boolean

    dTime = TimeValue("01:00:00")
    aTimes = ActiveSheet.Range("B3:F9").Value 'Change to the full table range
    ReDim aSumResults(1 To UBound(aTimes, 1) - LBound(aTimes, 1) + 1, 1 To 1)

    For i = LBound(aTimes, 1) To UBound(aTimes, 1)
        'Each i represents a row of the data
        'Go through each column and collect the conditional sums
        lResultIndex = lResultIndex + 1
        bFirst = True   'Use bFirst to ignore first value greater than dTime
        For j = LBound(aTimes, 2) To UBound(aTimes, 2)
            If aTimes(i, j) > dTime Then
                If bFirst Then
                    'This if the first value found for the row, ignore it
                    bFirst = False
                Else
                    'Not the first value found, include in sum
                    aSumResults(lResultIndex, 1) = aSumResults(lResultIndex, 1) + aTimes(i, j) - dTime
                End If
            End If
        Next j
    Next i

    'Output the results
    ActiveSheet.Range("G3").Resize(UBound(aSumResults, 1)).Value = aSumResults

End Sub

这篇关于Excel VBA - 如果值存在> x，加总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！