locked
Changes to InStr function in VBA? RRS feed

  • Question

  • One of my macros I have been using for a while, (no I did not change it) does no longer work.

    I think something is wrong with the compare argument "1". According to MSDN option "1" is used for vbTextCompare.

    However this line of code here gives me the error "Type Mismatch"

    i = instr("tabvs", "aaa7nw04",1)

    I think it is pretty obvious that both "tabvs" and "aaa7nw04" are both strings :)

    while this line of code works

    i = instr("tabvs", "aaa7nw04")

    (resulting in i=0 as "aaa7nw04" is not contained in "tabvs")

    So what am I doing wrong with setting the compare argument to 1?

    Thank you!

    Regards

    Roman

    Friday, September 9, 2016 12:48 PM

Answers

  • If you specify the Compare argument, you must also specify the Start argument:

    i = InStr(1, "tabvs", "aaa7nw04", 1)

    or, for better readability:

    i = InStr(1, "tabvs", "aaa7nw04", vbTextCompare)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, September 9, 2016 1:02 PM

All replies

  • If you specify the Compare argument, you must also specify the Start argument:

    i = InStr(1, "tabvs", "aaa7nw04", 1)

    or, for better readability:

    i = InStr(1, "tabvs", "aaa7nw04", vbTextCompare)


    Regards, Hans Vogelaar (http://www.eileenslounge.com)


    Friday, September 9, 2016 1:02 PM
  • Wow, thanks a lot! I wonder why my code ever worked then :)
    Friday, September 9, 2016 1:13 PM