Average with vba

[slamet Harto]

Hi there,

I want to create a formula using vba for counting how many calls being made by the user.

Please find the attached for your reference.

Many thanks in advance.
Rgds,
harto

[mdmackillop]

I'm not clear if you want a Count or an Average. Either CountIf or AverageIf would seem to suffice though.

[Bob Phillips]

What is wrong with your formula?

[Bob Phillips]

This array formula is better

=IF(COUNTIF(A$4:$A5,A5)=1,0,AVERAGE(IF($A$4:$A5=$A5,$C$4:$C5)))

[slamet Harto]

Hi Malcom,

Can you advise me to use Average function with VBA for this case.

User N made 2 calls (in total) then averaging by 2 Row. As result would be 2/2=1 (in average)


Appreciate your help.

[Bob Phillips]

Just add the formula via VBA



Range("J5").FormulaArray = "=IF(COUNTIF(A$4:$A5,A5)=1,0,AVERAGE(IF($A$4:$A5=$A5,$C$4:$C5)))"
Range("J5").AutoFill Range("J5:J14")

[slamet Harto]

HI Bob,

as the data retrieve by an Query, then I need a vba code to auto populate.


Dim newrc As New ADODB.Recordset
Dim i As Long
Sheets("ReportActivity").Select
[a3:H3].Select
Selection.AutoFilter

Range(Cells(3, 1), Cells(3, 10)) = _
Array("USER NAME", "DATE", "#of CALL", "Make Appt", "%", "Effective Calls", "%", "In-Effective Calls", "%", "Ave calls")
Cells(3, 1).Resize(1, 10).Select
Selection.HorizontalAlignment = xlCenter


Call Connect


Range("A4").Select
Queryku = "select * from X_4_Final_Sum_CALL_And_Appt_Monthly"

i = 4
With newrc

Set RsCluster = Cn.Execute(Queryku)

If RsCluster.EOF Then
MsgBox "Done", vbNullString
Exit Sub
End If


.Open Queryku, Cn, adOpenDynamic, adLockOptimistic, adCmdText

Do While Not .BOF And Not .EOF
Cells(i, 1).Value = Trim(!UserName)
Cells(i, 2).Value = Trim(!Year) & Trim(!Month) & Trim(!Date)
Cells(i, 3).Value = Format(![Total Call], "#,##0")
Cells(i, 4).Value = Format(![ApptDate], "#,##0")
Cells(i, 5).Formula = Format(Cells(i, 4).Value / Cells(i, 3).Value, "0%")

Dim NRow As Long
NRow = Cells(Rows.Count, "A").End(xlUp).row - 2
With Range("F4").Resize(NRow)
.Formula = "=SUMPRODUCT(--(RC[-5]=DetailsA),--(RC[-4]=DetailsB),--(""Contacted""=DetailsD))"
End With
With Range("H4").Resize(NRow)
.Formula = "=SUMPRODUCT(--(RC[-7]=DetailsA),--(RC[-6]=DetailsB),--(""UnContacted""=DetailsD))"
End With

Cells(i, 7).Formula = Format(Cells(i, 6).Value / Cells(i, 3).Value, "0%")
Cells(i, 9).Formula = Format(Cells(i, 8).Value / Cells(i, 3).Value, "0%")

[slamet Harto]

Wow, thanks for quick reply, Bob
ok. will try first.