CODEWITHSUNDEEP
excelvba
ajor
ajor

Reputation: 1634

UBound Value of Filter Output in VBA

I want to search a 1D array for a value, and I've been trying to do this using the filter function, but when I write a small sub to test this, it doesn't work and I get a "type mismatch" error.

Can anyone tell me what's wrong in this code?

Dim range1() As Variant
range1 = Sheet4.range("B3:B7")

Dim string1 As String
string1 = "hello"

If UBound(Filter(range1, string1)) >= 0 Then
    Sheet4.range("A1").Value = "Hello exists."
Else
    Sheet4.range("A1").Value = "Hello does not exist."
End If

Hello appears once in the selected range, so the result of the Ubound function should be 0, right?

Upvotes: 2

Views: 1551

Answers (1)

L42
L42

Reputation: 19727

You need to use transpose to create a 1D array out of a range. See this link

range1 = Application.Transpose(Sheet4.Range("B3:B7"))

As per MSDN Filter Function requires one-dimensional array of strings.

Required. One-dimensional array of strings to be searched

Upvotes: 4

Related Questions