excelif-statementrangevba

Excel VBA If range.value = something then

What I am looking for is this -

If a value in range of cells equals to some value or same value then it should show "Positive" else "Negative. But when I write like below syntax, it throws an error -

If range("F3:H5").value = "X" then

Msgbox "Positive result"

else

Msgbox "Negative result"

end if
Solution

  • You can use this function

        Function EvalRange(inRng As Range, inVal As Variant) As Variant

    Dim CntAll, CntMatch As Double

    CntAll = Application.Count(inRng)
    CntMatch = Application.CountIf(inRng, inVal)

    If CntAll = CntMatch Then
        EvalRange = "Positive Result"
        Else: EvalRange = "Negative Result"
    End If

    End Function