AverageIf

[hansgrandia]

Hello,

If I run below standing code in Sheets("Blad2"), it only displays the 4, 35 and 44 without doing the calculation. The code goes to "end sub" the moment is has touched For y = 2 To x. If I run the code in Sheets("Blad1") everything runs als expectated. What should I do to have the average of the customer regardless in which sheet I press the button?

Thank you. Regards,
Hans Grandia
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


x = Cells(Rows.Count, 1).End(xlUp).Row


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


End Sub

 

[JoeMo]

What is the value of x when you run the code and it jumps to End Sub at the line:

For y = 2 to x

??

 

[hansgrandia]

Great answer. Thanks for not giving me the answer immediately. This is learnfull. I brought down the piece of code in the script and everything runs perfectly.
Reegards,
Hans Grandia
Netherlands