S
scottwilsonx
Hello everyone, I have the following vba code, which takes the value i
column D and column C and calculates the difference.
Then for each occurance of a certain value in column V it calculate
the average difference between D anc C.
What I want to add to the code is a line which says that where th
difference between D and C is 0 (zero) the line is excluded from an
calculation of the average.
Hope this makes sense. All help greatfully received.
Sub avgtime()
Range("B9:G20000").ClearContents
a = 3
b = 3
c = (Worksheets("data").Cells(a, 4) - Worksheets("data").Cells(a
3).Value)
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
b = 9
Do Until Cells(b, 2) = Empty
If Cells(b, 2) = Worksheets("data").Cells(a, 22) The
GoTo 99
b = b + 1
Loop
Cells(b, 2) = Worksheets("data").Cells(a, 22)
End If
End If
99 a = a + 1
Loop
b = 9
Do Until Cells(b, 2) = Empty
a = 3
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
timesum = timesum + (Worksheets("data").Cells(a
4).Value - Worksheets("data").Cells(a, 3).Value)
items = items + 1
End If
End If
End If
a = a + 1
Loop
If items = Empty Then items = 1
Cells(b, 6) = items
Cells(b, 3) = timesum / items
Cells(b, 7) = items * (timesum / items)
timesum = 0
items = 0
b = b + 1
Loop
Call SortValues
Call TrimCalc
End Su
column D and column C and calculates the difference.
Then for each occurance of a certain value in column V it calculate
the average difference between D anc C.
What I want to add to the code is a line which says that where th
difference between D and C is 0 (zero) the line is excluded from an
calculation of the average.
Hope this makes sense. All help greatfully received.
Sub avgtime()
Range("B9:G20000").ClearContents
a = 3
b = 3
c = (Worksheets("data").Cells(a, 4) - Worksheets("data").Cells(a
3).Value)
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
b = 9
Do Until Cells(b, 2) = Empty
If Cells(b, 2) = Worksheets("data").Cells(a, 22) The
GoTo 99
b = b + 1
Loop
Cells(b, 2) = Worksheets("data").Cells(a, 22)
End If
End If
99 a = a + 1
Loop
b = 9
Do Until Cells(b, 2) = Empty
a = 3
Do Until Worksheets("data").Cells(a, 19) = Empty
If Worksheets("data").Cells(a, 19) = Cells(3, 3) Or Cells(3, 3)
Empty Then
If Worksheets("data").Cells(a, 36) = Cells(4, 3) Or Cells(4, 3
= Empty Then
If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
timesum = timesum + (Worksheets("data").Cells(a
4).Value - Worksheets("data").Cells(a, 3).Value)
items = items + 1
End If
End If
End If
a = a + 1
Loop
If items = Empty Then items = 1
Cells(b, 6) = items
Cells(b, 3) = timesum / items
Cells(b, 7) = items * (timesum / items)
timesum = 0
items = 0
b = b + 1
Loop
Call SortValues
Call TrimCalc
End Su