Hi all,

I am trying to write a macro that iterates through a dataset and averages values in one column based on values found in another column. I've been trying to use the AVERAGEIF function, but I cannot for the life of me figure out how to use a variable (in this case, variable i) in the function.

Here's my code snippet:

-----------------

Dim i As Long
Dim z As Long

z = 13

For i = 400 To 1000

'Set the D column value to the iteration number
Range("D" & z).Value = i

'Set the corresponding cell in the E column to the average of the corresponding values

Range("E" & z).Select
ActiveCell.Formula = "=AVERAGEIF($A$13:$A$3173, " = "&i, $B$13:$B$3173)"

z = z + 1

Next

-------------------

This returns a value of FALSE where the AVERAGEIF output should go.

I've also tried it in the form of:

=AVERAGEIF($A$13:$A$3173, i, $B$13:$B$3173)

This returns a value of #DIV/0.

I also tried putting the a reference to a cell containing an absolute version of the i variable's value, like this:

=AVERAGEIF($A$13:$A$3173, "="&D13, $B$13:$B$3173)

And it still returns a value of false.

The only time I can actually get it to work is when I input the numerical value (Eg. 400 instead of i), but I really don't feel like typing out this chunk of code 600 times - that's why I am doing the iterative For loop.

Could anyone please help me understand how to use this variable in the AVERAGEIF function? I've been trying this for hours and cannot figure it out.

Thanks!