vba filter function returning runtime error 13: type mismatch

Justin Doward · Oct 14th 2019

In a userform (VIEWM) I am adding a variable range "B4:B88" at the moment from sheet "CROSSREF" to create an array named SrchMe, I then want filter to compare a textbox value "FndThis" to the array and return an array of matches called "OutP1".
Both of the message boxes return appropriate responses, option explicit is at top of page.

The cells contain chemical reference numbers called CAS numbers in the format 123-45-6 or similar, but I have changed the array to contain text and this also fails.

Any help appreciated.

Code

Private Sub CASNAMESCH_Change()


Dim FndThis As String
Dim LastRw As Integer
Dim SrchMe(), OutP1() As Variant


LastRw = Sheets("CROSSREF").Cells(1, 1).End(xlDown).Row


SrchMe = Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2))


FndThis = VIEWM.CASNAMESCH.Value


MsgBox (SrchMe(1, 1))
MsgBox (FndThis)


OutP1 = Filter(SrchMe, FndThis)  'MISMATCH ERROR on this line.


MsgBox (OutP1(1, 1))




End Sub

Display More

Regards
Justin

**pike** · Oct 14th 2019

Hello did you try,,

Code

Dim SrchMe() As String, OutP1() As String


SrchMe = Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).value

Justin Doward · Oct 14th 2019

Thank you for the response pike,

I just tried that but the result is a type mismatch earlier in the code, at this line:

Code

SrchMe = Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).value

I am not sure why that would work though, why would you declare the arrays as strings?

cheers
Justin

**pike** · Oct 14th 2019

Hi , The array should be

Code

Dim SrchMe as Variant, OutP1 as variant


SrchMe = Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).value

as its not dim or redim

Justin Doward · Oct 15th 2019

That change does not alter the outcome, I have uploaded an example that is similar to what I am trying to do. The output from OutP1 will then form the .list for a list box.

Thank you for having a look, let me know if I should start a new thread... I thought it would be a simple fix and I guess it is related to having a volatile range for the array but the array "ScheMe" does seem to be created fine just the filter does not recognise it?

cheers
Justin

**pike** · Oct 15th 2019

Yes won’t be much and will be easier with file

**pike** · Oct 15th 2019

the array needed transposing

Code

Option Explicit


Private Sub CASNAMESCH_Change()
Dim FndThis As String
Dim LastRw As Integer
Dim SrchMe As Variant, OutP1 As Variant


LastRw = Sheets("CROSSREF").Cells(1, 1).End(xlDown).Row


SrchMe = Application.Transpose(Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).Value)


FndThis = VIEWM.CASNAMESCH.Value


MsgBox (SrchMe(10) & " " & SrchMe(2))
MsgBox (FndThis)


OutP1 = Filter(SrchMe, FndThis)


MsgBox (OutP1(0))
End Sub

Display More

Justin Doward · Oct 15th 2019

Thank you very much for that, it is working well... not exactly sure why but hopefully it will become clear with time.

I will keep in mind the RSPCA the next time I donate.

Best regards
Justin

**pike** · Oct 15th 2019

your array goes down one column, filter needs array to be flat and across One dimension and with zero base

**pike** · Oct 15th 2019

aslo try with a combobox

Code

Private Sub ComboBox1_Change()
Dim i As Long
Dim LastRw As Integer
LastRw = Sheets("CROSSREF").Cells(1, 1).End(xlDown).Row
    With Me.ComboBox1
        If Not IsArrow Then .List = Application.Transpose(Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).Value)
        If .ListIndex = -1 And Len(.Text) Then
            For i = .ListCount - 1 To 0 Step -1
                If InStr(1, .List(i), .Text, 1) = 0 Then .RemoveItem i
            Next i
            .DropDown
        End If
    End With
End Sub


Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Dim i As Long
Dim LastRw As Integer
LastRw = Sheets("CROSSREF").Cells(1, 1).End(xlDown).Row
IsArrow = KeyCode = vbKeyUp Or KeyCode = vbKeyDown
If KeyCode = vbKeyReturn Then Me.ComboBox1.List = Application.Transpose(Range(Sheets("CROSSREF").Cells(4, 2), Sheets("CROSSREF").Cells(LastRw, 2)).Value)
End Sub

Display More

Justin Doward · Oct 15th 2019

Lovely code, thank you. Still deciphering it but it works a charm.

Thank you again.

Justin

vba filter function returning runtime error 13: type mismatch

Participate now!

Share