HELP NEEDED- Macro to return average values from a variable range fulfilling OR condition

[hamdyeleraky]

Hello ,

I am in urgent need of help. I am quite new to VBA so it might be something obvious but appreciate all comments
There is a dynamic range of data in "Mastersheet" in Column "I" that i would like to get their average and return it back to another sheet "Charts" in a specific cell (B1) but i would like to only average data that fulfill one OR two string conditions (Mastersheet in Column D and E)

It return an error that i am unable to debug (only that the issue is in set s1...) and i have tried normal AverageIFS and it return average for the conditions having an AND relation not OR

Please help
Thanks!
[Sub Avg()Dim x As Integer
Dim s1, s2
Dim lrow As Long


Set s1 = Worksheets("Charts")
Set s2 = Worksheets("mastersheet")
lrow = s2.Cells(Rows.Count, "C").End(xlUp).Row


For x = 2 To lrow
If s2.Range("D" & x) = "GoGreen" Or s2.Range("E" & x) = "GoGreen" Then

s1.B8.Formula = "=Average(s2.I1:I10)"
End If
Next x


End Sub]

 

[Momentman]

Without going too far, the error is on this line

Set S1 = Worksheets("Charts")

right?

Do you have a sheet called "Charts"?

 

[hamdyeleraky]

Thanks for fast response! yes i have this sheet called "Charts" in which i want to store the result

 

[Momentman]

Thanks for fast response! yes i have this sheet called "Charts" in which i want to store the result

on what line do you get an error?

 

[hamdyeleraky]

on what line do you get an error?

Set S1 = Worksheets("Charts")
But not sure if the rest of the code is working