Run Time Error 13 "Type Mismatch"

  • In the attached workbook, I needed to make a change to one of the “names” – instead of using the name “Avaya Virtual Host ” – I need to drop the word Avaya and go with just “Virtual Host ”. When I made the change to the command button 1 code (labeled Virtual Hosts), the macro runs as expected. I’m prompted to enter a QTY of hosts and the macro copies rows 27:37 and pastes them immediately below and updates the numerical identifier for that particular host.


    The error happens when I go back and try to add any QTY of additional hosts - I get a run-time error 13 “type mismatch”. I’ve looked at the debugger and it points to this line in the code “ Range("A" & y) = "Virtual Host " & Mid(fnd, 20, 999) + z” - I can’t figure out why this is not working when the only thing I’ve changed is the name from “Avaya Virtual Host ” to “Virtual Host ” throughout the code.



  • Try replacing this line of code:

    Code
    1. Range("A" & y) = "Virtual Host " & Mid(fnd, 20, 999) + z

    with this line:

    Code
    1. Range("A" & y) = "Virtual Host " & Mid(fnd, 13, 999) + z

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.

  • Thanks Mumps,



    I added the code to the CommandButton1 code and I now get a different error when attempting to add any virtual hosts the first time. Run-time error ‘91’: “Object Variable or With block variable not set. It points me to this line in the code



    y = fnd.Row + 11



    Also, just so I can understand, you changed the 20 to 13 – what does that number represent?

  • Code
    1. Mid(fnd, 13, 999) + 1

    This code extracts the number at the end of the variable fnd which is something like the string "Virtual Host 2". The 13 is the starting point in the string where the extraction starts which in this case is the space after the second "t" in the string. Therefore the "2" is extracted and a "1" is added to it which results in "Virtual Host 3". I hope this makes sense.

    Try this revised macro:

    You can say "THANK YOU" for help received by clicking the :thumbup: icon in the bottom right corner of the helper's post.
    Practice makes perfect. I am very far from perfect so I'm still practising.