VBA: Counting an "if" loop to get average.

[HeloNatebour]

Hi im learning how to program in VBA and i have a problem with this code...
I need to count the even number of X random Cells then have to do an average of this numbers. My problem is, how count all even numbers to divide it by the sum of all.


Here is my code....


Sub Numbers()
Dim row, op, add As Integer
row = 1
For coun = 1 To 5
op = Cells(row, 1).Value Mod 2
If op = 1 Then
add = Cells(row, 1).Value + next
ElseIf op = 0 Then
addim = Cells(row, 1).Value + next2
End If
next2 = addim
next = add
row = row + 1
avg = addim / **** < Here is my problem, how can i count all the even numbers in the loop to divide by, in the expresion?
Next
Range("C1").Value = add
Range("D1").Value = avg
End Sub

Thanks in advance

[Paul_Hossler]

Couple of suggestions, based on personal opinion and style, to consider





'Use Option Explicit to require all variables be defined and help prevent typos
Option Explicit

Sub Numbers()


'Long better than Integer and EACH must be ' ...As something' else assumed to be Variant
'row is VBA keyword. I'd make the variable names more descriptive
'Dim row, op, add As Long

Dim DataRow As Long, EvenTotal As Long, EvenCount As Long

'Loop from 1 to 5 <<<<< "Bad Comment since the code is very obvious and the just clutters the macro
' Besides extra work if/when you change to go up to 500
For DataRow = 1 To 5
'here you can just use the even/odd
'Also good comments always good. e.g.
' "Even numbers have Mod 2 = 0" (<<<<<< Good comment)
If (Cells(DataRow, 1).Value Mod 2) = 0 Then
'I think you were trying to make 1 variable do the work of 2
EvenCount = EvenCount + 1
EvenTotal = EvenTotal + Cells(DataRow, 1).Value
End If
Next
Range("C1").Value = EvenCount
Range("D1").Value = EvenTotal / EvenCount
End Sub

[HeloNatebour]

Hey thanks, it helped me a lot. The tips were very useful too. Greetings

[snb]

To contribute to your learning curve:


Sub M_snb()
sn = Columns(1).SpecialCells(2, 1)

For j = 1 To UBound(sn)
If sn(j, 1) Mod 2 = 0 Then
y = y + sn(j, 1)
t = t + 1
End If
Next

MsgBox "number of even values: " & t & vbLf & "total of even values: " & y & vbLf & "average: " & y / t
End Sub

Or


Sub M_snb()
Columns(1).SpecialCells(2, 1).Name = "snb"

MsgBox "number of even values: " & [SUMPRODUCT(N(MOD(snb,2)=0))] & vbLf & "total of even values: " & [SUMPRODUCT((snb)*(MOD(snb,2)=0))] & vbLf & "average of even values: " & [SUMPRODUCT((snb)*(MOD(snb,2)=0))/SUMPRODUCT(N(MOD(snb,2)=0))]
End Sub