Run Time Error 13 "Type Mismatch"

mike_calc · Oct 30th 2020

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.

Mumps · Oct 30th 2020

Try replacing this line of code:

Code

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

with this line:

Code

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

mike_calc · Oct 30th 2020

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?

Mumps · Oct 30th 2020

Code

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:

Code

Private Sub CommandButton1_Click()
'Host Command Button.  Allows users to choose # of hosts and VBA code will automatically create
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    Dim numrow As String, fnd As Range, y As Long, z As Long, x As Long: x = 38
    numrow = Application.InputBox("How many hosts are being added?", "Hosts", , , , , , 1)
    If numrow = False Then Exit Sub
    If WorksheetFunction.CountIf(Range("A:A"), "*Virtual Host*") = 1 Then
        If IsNumeric(numrow) And numrow = 1 Then
                Rows("27:32").Hidden = False
                Rows("33:36").Hidden = True
                Rows("37:37").Hidden = False
            
        ElseIf IsNumeric(numrow) And numrow > 1 Then
            
             Rows("27:32").Hidden = False
                Rows("33:36").Hidden = True
                Rows("37:37").Hidden = False
            For i = 1 To numrow - 1
                Rows("27:37").Copy
                Rows(x).EntireRow.Insert Shift:=xlDown
                Range("A" & x) = "Virtual Host " & i + 1
                Range("A" & x + 1).Resize(10, 16).ClearContents
                x = x + 11
            Next i
        End If
    Else
        Set fnd = Range("A:A").Find(What:="Virtual Host", LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlPrevious)
        If IsNumeric(numrow) And numrow = 1 Then
                Rows("27:32").Hidden = False
                Rows("33:36").Hidden = True
                Rows("37:37").Hidden = False
            Rows("27:37").Copy
            Rows(fnd.Row + 11).EntireRow.Insert Shift:=xlDown
            Range("A" & fnd.Row + 11) = "Virtual Host " & Mid(fnd, 13, 999) + 1
            Range("A" & fnd.Row + 12).Resize(10, 16).ClearContents
        ElseIf IsNumeric(numrow) And numrow > 1 Then
            y = fnd.Row + 11
            z = 1
            Rows("27:32").Hidden = False
                Rows("33:36").Hidden = True
                Rows("37:37").Hidden = False
            For i = 1 To numrow
                Rows("27:37").Copy
                Rows(y).EntireRow.Insert Shift:=xlDown
                Range("A" & y) = "Virtual Host " & Mid(fnd, 13, 999) + z
                Range("A" & y + 1).Resize(10, 16).ClearContents
                y = y + 11
                z = z + 1
            Next i
        End If
    End If
    With Application
        .EnableEvents = True
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Display More

Run Time Error 13 "Type Mismatch"

Participate now!

Share