Printable View
Hello,
I am trying to write a code that calculates the average price based on specific criteria. The lines will always change so it needs to be a general code that says for all the same account in column A, if the security in column C is the same, then average the prices listed in column K and place that number in column O.
Attached is a example of the sheet I am working with and how I want to average the price.
In O6, use
=AVERAGEIFS($K$1:$K$28,$A$1:$A$28,$A6,$B$1:$B$28,$B6,$C$1:$C$28,$C6)
That works, I placed the formula in this code and it seems to be generic enough to work with all different output each time.
Code:Sub TestAverageif()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
With .Cells(i, "O").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R[-5]C[-4]:R[21]C[-4],R[-5]C[-14]:R[21]C[-14],R[-1]C[-14],R[-5]C[-13]:R[21]C[-13],R[-1]C[-13],R[-5]C[-12]:R[21]C[-12],R[-1]C[-12])"
End With
Next i
End With
End Sub
Actually this doesn't seem to be working. In cell O1 the formula is =AVERAGEIFS(K23:K1048573,A23:A1048573,A1,B23:B1048573,B1,C23:C1048573,C1). It should always averaging K2 down to lastrow in K if the values in A are the same and the values in C are the same. This keeps shifting down starting at K23 which is not correct
I think something like this will work:
Code:Sub TestAverageif()
Dim Lastrow As Long
Dim i As Long
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
For i = 2 To Lastrow
With .Cells(i, "O").Select
ActiveCell.FormulaR1C1 = "=AVERAGEIFS(R2C11:R1048576C11,R2C1:R1048576C1,R2C1:R1048576C1,R2C3:R1048576C3,R2C3:R1048576C3)"
End With
Next i
End With
End Sub
Just use
Code:Sub TestAverageif()
Dim Lastrow As Long
With ActiveSheet
Lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row
.Range("O2").Resize(Lastrow-1).Formula = "=AVERAGEIFS($K$2:$K$" & Lastrow & ",$A$2:$A$" & Lastrow & ",$A2,$B$2:$B$" & Lastrow & ",$B2,$C$2:$C$" & Lastrow & ",$C6)"
End With
End Sub