vba filter function returning runtime error 13: type mismatch

  • 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.



    Regards
    Justin

  • 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
    1. 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

  • 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

    Files

    • TEST1.xlsm

      (23.75 kB, downloaded 135 times, last: )
  • the array needed transposing

  • aslo try with a combobox