VBA: AverageIf

[hansgrandia]

Hello,

I have worked out below standing code, but it's ignoring the AverageIf part of it. What did I do wrong here? The cells (average) remain empty without error message.

Thank you for your help!

Regards,
Hans
Netherlands

.....................

Sub ReturnLeadTime()


Dim Data As Worksheet
Dim Result As Worksheet
Dim GemLT As Range
Dim Customers As Range
Dim y As Long
Dim x As Long


Set Data = Sheets("Blad1")
Set Result = Sheets("Blad2")
Set Customers = Worksheets("Blad1").Range("A2:A51")
Set GemLT = Worksheets("Blad1").Range("E2:E51")


'Subcategories in Column A
Result.Range("A1").Value = "Customer"
Result.Range("B1").Value = "Average"
Result.Range("A2").Value = "4"
Result.Range("A3").Value = "35"
Result.Range("A4").Value = "44"


'Per subcategorie totaal berekenen


For y = 2 To x


If Cells(y, "A") <> "" Then
Cells(y, 2).Value = WorksheetFunction.AverageIf(Customers, Range("A" & y), GemLT)
End If
Next y


End Sub

 

[Jonmo1]

Perhaps you need to qualify the sheet in that loop code..
I would guess it should be the Result Sheet

If Result.Cells(y, "A") <> "" Then
    Result.Cells(y, 2).Value = WorksheetFunction.AverageIf(Customers, Result.Range("A" & y), GemLT)
End If
Next y

 

[nils7]

I think you have ommitted to include a value for "x". You define the variable, but there is no actual value given to it.

 

[Jonmo1]

I think you have ommitted to include a value for "x". You define the variable, but there is no actual value given to it.

That too...
Good catch.

 

[hansgrandia]

Thanks, works perfectly!