I am trying to write a function that finds the maximum value in a range, where a condition is met in another range. The ranges are of equal size.
What i am trying to do is look along a row of week ending performance stats that span a year and find the maximum value for say january.
I have a row with the dates the weeks end on, and then a row that uses the MONTH function to determine the month that that week falls in.
3 rows below that i have a row of values.
so i attempted this, but it causes my Excel to lock and Dr Watson.
whereabouts would be the range that the month function is in, condition would be 1 for January, rowsdown would be 4.
Function findmax(whereabouts as Range, condition as single, rowsdown as single) as Long
Dim x as Long
x = 0
For Each C in whereabouts
If Activecell.Value = condition Then
If Activecell(rowsdown, 1).Value > x Then
x = Activecell(rowsdown, 1).Value
End If
End If
Next C
findmax = x
End Function
But no luck, i'm thinking the Activecell is the problem, but what should i use?
Thanks
What i am trying to do is look along a row of week ending performance stats that span a year and find the maximum value for say january.
I have a row with the dates the weeks end on, and then a row that uses the MONTH function to determine the month that that week falls in.
3 rows below that i have a row of values.
so i attempted this, but it causes my Excel to lock and Dr Watson.
whereabouts would be the range that the month function is in, condition would be 1 for January, rowsdown would be 4.
Function findmax(whereabouts as Range, condition as single, rowsdown as single) as Long
Dim x as Long
x = 0
For Each C in whereabouts
If Activecell.Value = condition Then
If Activecell(rowsdown, 1).Value > x Then
x = Activecell(rowsdown, 1).Value
End If
End If
Next C
findmax = x
End Function
But no luck, i'm thinking the Activecell is the problem, but what should i use?
Thanks
