Contiguous Cell Value in VBA

Question

Is it possible to compare contiguous cell values with single cell? For example:

If Sheets("Sheet2").Range("E4:E6").Value = Sheets("Sheet1").Range("D4").Value Then

....

I tried this but showing error TYPEMISMATCH

Answer 1

The expression Sheets("Sheet2").Range("E4:E6").Value returns a two dimensional array containing the Values in those cells. You can't compare a value to an array with the equal operator as you've seen.

If you want to see if that value exists, you can use the Match worksheet function like this

Sub FindMatch()

    Dim lMatch As Long

    On Error Resume Next
        lMatch = Application.WorksheetFunction.Match(Range("D4").Value, Range("E4:E6").Value, False)
    On Error GoTo 0

    If lMatch > 0 Then
        Debug.Print "Value exists"
    Else
        Debug.Print "Not included"
    End If

End Sub

If you're trying to determine if all the values in the range are the same, you can use Sumif like this

Sub FindAllMatch()

    Dim dSum As Double

    If Application.WorksheetFunction.Sum(Range("E4:E6")) = Application.WorksheetFunction.SumIf(Range("E4:E6"), Range("D4").Value) Then
        Debug.Print "All match"
    Else
        Debug.Print "One doesn't match"
    End If

End Sub

Another option is the Filter VBA function that returns an array filtered on some criteria. Filter requires a 1-d array and Range().Value returns a 2-d array, so you have to use the Transpose function to make that conversion.

Sub Findmatch2()

    Dim vaOneD As Variant
    Dim sMatch As String

    sMatch = Range("D4").Value

    vaOneD = Application.WorksheetFunction.Transpose(Range("E4:E6").Value)

    If UBound(Filter(vaOneD, sMatch, True)) > -1 Then
        Debug.Print "There's a match"
    Else
        Debug.Print "No match"
    End If

End Sub

To check if all of the value are the same, change the appropriate line to

If UBound(Filter(vaOneD, sMatch, True)) - LBound(Filter(vaOneD, sMatch, True)) = UBound(vaOneD) - LBound(vaOneD) Then