Hi, Im having a problem with using range variables, basically the CriteriaValue variable refuses to work correctly, & gives errors whenever its called ... Im pretty sure its the syntax, & not sure how to fix it ...
This is a basic countif script, but cant get the main variable to use the countif to work, this is stored in CriteriaValue.
Thanks
Sub ValleyDips()
Dim ValuesRange As Range
Dim ResultCell As Range
Dim CriteriaValue As Range
Set ValuesRange = Range("A1:A4")
Set ResultCell = Range("C3")
Set CriteriaValue = Range("A1")
HighestCurentValue = Range(CriteriaValue).Value
For j = 1 To 4
ResultCell = Application.WorksheetFunction.CountIf(ValuesRange, "<" & Range(CriteriaValue))
If ResultCell > 3 Then
d = ValuesRange.Offset(i, 0).Address
i = i + 1
MsgBox d & HighestCurentValue
End If
If ResultCell < 3 Then
d = CriteriaValue.Offset(i, 0).Address
i = i + 1
MsgBox d & HighestCurentValue
End If
Next j
End Sub
Bookmarks