help with excluding 0s from code

  • Thread starter Thread starter scottwilsonx
  • Start date Start date
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
 
dim diff as double
Const zero As Double = 0.00001

If Worksheets("data").Cells(a, 22) = Cells(b, 2) Then
diff = Worksheets("data").Cells(a,4).Value - _
Worksheets("data").Cells(a, 3).Value
if abs(diff) > zero then
timesum = timesum + diff
items = items + 1
End if
End If


if you are just working with integers, then you could use

dim diff as Long


if diff <> 0 then
 
Back
Top