Check if string is in range Excel-VBA with Filter command

Question

Im trying to check if a range of cells each have a value defined in another range

This is my current code:

Sub CheckInstallationName()

    Dim LastRow As Long

    With Worksheets(2)
        LastRow = .Cells(.Rows.Count, Worksheets(1).Cells(4, 3).Value).End(xlUp).Row
    End With

    Dim rngA As Range
    Set rngA = Range(Worksheets(1).Cells(4, 3).Value & "4:" & Worksheets(1).Cells(4, 3).Value & LastRow)

    Dim cellA As Range
    Dim InstallationNameRange As Variant

    InstallationNameRange = Worksheets(1).Range("B16:B32").Value

    For Each cellA In rngA
        If UBound(Filter(InstallationNameRange, cellA.Value)) < 0 Then
            'Some code
        End If
    Next cellA

End Sub

On the If UBound(filter(InstallationNameRange, cellA.Value)) < 0 Then I get the error "Run-time error '13': Type mismatch" and cannot find a solution for this. Probably it is a very small fix. Without this if-statement the code works

Do `Debug.Print UBound(filter(InstallationNameRange, cellA.Value))` before the `If`. If it is an error then that is the likely cause of the error you are getting. — Robin Mackenzie, Oct 05 '17 at 10:05
@Vityata Filter is a standard function in VBA Filter( SourceArray, Match, [Include], [Compare] ) https://msdn.microsoft.com/en-us/library/fat7fw0s(v=vs.90).aspx — SLT, Oct 05 '17 at 10:50
@RobinMackenzie with that line before the `IF` I get the same error still — SLT, Oct 05 '17 at 10:58

Vityata · Accepted Answer · 2017-10-05T11:11:24.197

1

Open a new Excel and write the following:

Public Sub CheckRangeInRange()

    Dim rngA        As Range
    Dim rngB        As Range
    Dim rngCellA    As Range
    Dim rngCellB    As Range
    Dim blnError    As Boolean

    Set rngA = Worksheets(1).Range("A1:B10")
    Set rngB = Worksheets(1).Range("D10:E20")

    rngA.Interior.Color = vbYellow
    rngB.Interior.Color = vbRed

    For Each rngCellA In rngA
        blnError = True
        For Each rngCellB In rngB
            If rngCellA = rngCellB Then
                blnError = False
            End If
        Next rngCellB
        If blnError Then Debug.Print "Display Error here!"
    Next rngCellA

End Sub

Put some values in A1:B10 and D10:E20 and the addresses of the matching values would be printed in the immediate window.

edited Oct 05 '17 at 11:11

answered Oct 05 '17 at 10:34

Vityata

42,633
8
55
100

I see what you are trying to do here and this was the first thing I did for my code, but this won't work for my intention. I want to display an error when the value is not in the array. This code will give an error for every other value in the array. – SLT Oct 05 '17 at 11:09
@SLT - that's why there are flags in VBA. See the edit. – Vityata Oct 05 '17 at 11:11
Ah, you are right, like this it will work, but I think for large ranges the solution of Robin Mackenzie is computing faster, so I am going to try that first – SLT Oct 05 '17 at 11:20
@SLT - depends what is your goal - if you build on my solution it can display any difference present, and the `.Filter` would tell you only `yes` or `no`. – Vityata Oct 05 '17 at 12:03

score 1 · Answer 2 · answered Oct 05 '17 at 11:07

1

You can't use Filter on a 2-d range and any array created from a Range is 2-d even if it is a single row or column.

You can use the 'double Transpose trick` per this question. Note the highly up-voted answer, not the accepted one.

E.g.:

Option Explicit

Sub Test()

    Dim rng As Range
    Set rng = Sheet2.Range("C20:E20") 'a, b, c

    ' use the double transpose trick to convert 2-d array to 1-d array
    Dim arr As Variant
    arr = Application.WorksheetFunction.Transpose( _
        Application.WorksheetFunction.Transpose(rng.Value))

    ' now Filter will work
    Dim out As Variant
    out = Filter(arr, "a")

    Debug.Print out(0)

End Sub

answered Oct 05 '17 at 11:07

Robin Mackenzie

18,801
7
38
56

I think you found the problem in my code, it is the Filter function. I got your function to work, but my Range is 1 column with multiple rows, so that would mean that I need `arr = Application.WorksheetFunction.Transpose(WorksheetFunction.Range(rng.Value))` right? This line however, does not want to work for me – SLT Oct 05 '17 at 11:51
A single `Tranpose` works in that test code so not sure why it doesn't work for you. You will, however, get Subscript out of range if (in your code) `cellA.Value` is not a value in `InstallationNameRange`. – Robin Mackenzie Oct 05 '17 at 11:56

Check if string is in range Excel-VBA with Filter command

2 Answers2