none
Mis-match error RRS feed

  • Question

  • I am very new to visual basic coding. My fcode is given below (it is my first attempt so i know it looks stupid):

    When i have input string "switchport trunk allowed vlan 401,3336,3372,3383", code works fine.

    However, if i have input string "switchport trunk allowed vlan 401,3336,3372-3383", code gives me "Runtime error 13, Type mismatch".

     

    Please help!



    Sub test1()

    Dim i As Integer

    Dim j As Integer

    Dim k As Integer

    Dim l As Integer

    Dim m As Integer

    Dim n As Integer

    Dim o As Integer

    Dim p As Integer

    Dim q As Integer

    Dim r As Integer

    Dim a As String

    Dim b As String

    Dim c As String

    Dim d As String

    Dim e As Boolean

    Dim Array1() As String

    Dim Array2() As String

    Dim Dummy As String

    i = 1

    l = 1

    For i = 1 To 15000

    a = Cells(i, 1)

    j = 0

    j = InStr(a, "interface GigabitEthernet")

    If j = 1 Then Cells(l, 4).Value = a
    If j = 1 Then l = l + 1

    k = 0

    k = InStr(a, "interface TenGigabitEthernet")

    If k = 1 Then Cells(l, 4).Value = a
    If k = 1 Then l = l + 1

    m = 0
    q = 8
    e = False


    m = InStr(a, "switchport trunk allowed vlan add")
    If m = 1 Then a = Replace(a, "switchport trunk allowed vlan add", "")
    If m = 1 Then a = Replace(a, ",", ";")
    If m = 1 Then a = Replace(a, "-", "@")
    If m = 1 Then a = a & ";50000"
    n = Len(a)
    n = n - 1
    If m = 1 Then Cells(l, 100) = n
    If m = 1 Then Cells(l, 5).Value = a
    If m = 1 Then l = l + 1
    If m = 1 Then Array1() = Split(a, ";")
    If m = 1 Then

    For p = 0 To 50

    If Array1(p) = 50000 Then

    p = p + 50

    Else

    Cells(l - 1, q) = Array1(p)

    End If

    q = q + 1

    Next p

    End If

    m = InStr(a, "switchport trunk allowed vlan ")
    If m = 1 Then a = Replace(a, "switchport trunk allowed vlan ", "")
    If m = 1 Then a = Replace(a, ",", ";")
    If m = 1 Then a = Replace(a, "-", "@")
    If m = 1 Then a = a & ";50000"
    n = Len(a)
    n = n - 1
    If m = 1 Then Cells(l, 100) = n
    If m = 1 Then Cells(l, 5).Value = a
    If m = 1 Then l = l + 1
    If m = 1 Then Array1() = Split(a, ";")
    If m = 1 Then

    For r = 0 To 50

    If Array1(r) = 50000 Then

    r = r + 50

    Else

    Cells(l - 1, q) = Array1(r)

    'Dummy = Array1(p)

    'r = InStr(Dummy, "-")

    'If r = 1 Then

    'Array2() = Split(Array1(p), "-")

    'Cells(l - 1, 20) = Array2(0)
    'Cells(l - 1, 21) = Array2(2)

    'End If

    End If

    q = q + 1

    Next r

    End If

    Next i

    End Sub



    • Edited by Atif Hafeez Thursday, November 3, 2011 1:19 AM
    Wednesday, November 2, 2011 9:51 PM

Answers

  • You are comparing string (Array1) with long (50000) so you have type mis-match error. String and long are different types.

    In your example for r = 4, Array1(r) = "2078-2480" and this throws an error. With r = 1 to 3 VBA converts both values to double and does the comparison, but it can not convert value with "-" in it as it is not numeric.

    You can compare two strings, so this

    If Array1(r) = "50000" Then

    will not cause type mis-match error as it is equivalent to if "2078-2480" = "50000" then

     

    In general it is not a very good idea to allow excel to automatically convert data as it can lead to unexpected errors. Try to always compare the same data types or use Type conversion functions so that the conversion is explicit.

    • Marked as answer by Atif Hafeez Friday, November 11, 2011 9:27 AM
    • Edited by Abcadlo Friday, November 11, 2011 9:27 AM
    Friday, November 11, 2011 9:05 AM

All replies

  • Hi Hafeez

    The code you have posted doesn't throw the error. My guess would be in some calculation post assigning to array

    Since you are converting comma's to semic-colons and splitting - it worked fine

    When you tried to do the same for 401,3336,3372-3383. It would have split as

    401

    3336

    3372-3383

    Obviously the last one is not an integer and when you try to do some calculations based on it throws the error

    HTH

    Cheers

    Shasur

     

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Thursday, November 3, 2011 8:46 AM
  • Dear Shasur,

     

    Thank you for having a look at the code.

     

    What you said make sense, but i am not doing any calculation on the values after splitting. All i did is; insert each value in different column i.e.

    When I tried to do the same for 401,3336,3372-3383. It should split as

    401

    3336

    3372-3383

    after doing this, i am just assigning each value in different cell. That is all. So, why it is giving mis-match error?


    Atif Hafeez Network Specialist Design Engineer
    Thursday, November 3, 2011 12:06 PM
  • hi,
     
    when you put (3372-3383 ) in a cell, Excel thinks you are trying to do a subtraction
     
    --
    isabelle
     Le 2011-11-03 08:06, Atif Hafeez a écrit :
     
    > 3372-3383
    >
    > So, why it is giving mis-match error?
     
    Thursday, November 3, 2011 12:34 PM
  • Dear Shasur,

     

    Thank you for having a look at the code.

     

    What you said make sense, but i am not doing any calculation on the values after splitting. All i did is; insert each value in different column i.e.

    When I tried to do the same for 401,3336,3372-3383. It should split as

    401

    3336

    3372-3383

    after doing this, i am just assigning each value in different cell. That is all. So, why it is giving mis-match error?


    Atif Hafeez Network Specialist Design Engineer


    Hi

    Can you place the value of string and post the code with the line that throws the error. I couldn't simulate the error here

     

    Cheers

    Shasur


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Friday, November 4, 2011 5:51 AM
  • Dear shasur,

    How can i know which line is throwing error?

    Best regards!


    Atif Hafeez Network Specialist Design Engineer
    Thursday, November 10, 2011 1:27 PM
  • Hi

    Press Debug in the Dialog box when the error is thrown. This will stop in the line that throws the error.

    You can all step into the code using F8 and check where error is thrown.

    If you still face problems please post the entire code here

    Cheers

    Shasur

     


    http://www.vbadud.blogspot.com http://www.dotnetdud.blogspot.com
    Friday, November 11, 2011 7:27 AM
  • Here is the entire code. (error line is highlighted)

     

    Input:

     

    interface GigabitEthernet1/1
    description link to ISAM-Olaya H-J (114-00-000)
    switchport
    switchport trunk encapsulation dot1q
    switchport trunk allowed vlan 99,1359,1599,2077,2078-2480
    switchport mode trunk
    switchport nonegotiate
    dampening 2 250 2000 10
    udld port disable
    wrr-queue bandwidth percent 27 35 38
    wrr-queue random-detect min-threshold 1 30 60 90 100 100 100 100 100
    wrr-queue random-detect max-threshold 1 100 100 100 100 100 100 100 100
    no wrr-queue random-detect 2
    no wrr-queue random-detect 3
    storm-control broadcast level 0.50
    storm-control multicast level 0.50
    no cdp enable
    spanning-tree bpdufilter enable

     

    Code:

    Sub test1()

    'Define an integer i for making a loop to select a range of cells.
    Dim i As Integer

    Dim j As Integer

    Dim k As Integer

    Dim l As Integer

    Dim m As Integer

    Dim n As Integer

    Dim o As Integer

    Dim p As Integer

    Dim q As Integer

    Dim r As Integer

    Dim s As Integer

    Dim t As Integer

    Dim u As Integer

    Dim a As String

    Dim b As String

    Dim c As String

    Dim d As String

    Dim e As Boolean

    Dim Array1() As String

    Dim Array2() As String

    Dim Dummy As String

    Dim Dummy1 As String

    Dummy = "/"

    i = 1

    l = 1

    For i = 1 To 15000

    a = Cells(i, 1)

    j = 0

    s = 0

    j = InStr(a, "interface GigabitEthernet")

    If j = 1 Then Cells(l, 4).Value = a
    If j = 1 Then l = l + 1

    k = 0

    k = InStr(a, "interface TenGigabitEthernet")

    If k = 1 Then Cells(l, 4).Value = a
    If k = 1 Then l = l + 1

    m = 0
    q = 8
    e = False


    m = InStr(a, "switchport trunk allowed vlan add")
    If m = 1 Then a = Replace(a, "switchport trunk allowed vlan add", "")
    If m = 1 Then a = Replace(a, ",", ";")
    'If m = 1 Then a = Replace(a, "-", "99990")
    If m = 1 Then a = a & ";50000"
    n = Len(a)
    n = n - 1
    If m = 1 Then Cells(l, 100) = n
    If m = 1 Then Cells(l, 5).Value = a
    If m = 1 Then l = l + 1
    If m = 1 Then Array1() = Split(a, ";")
    If m = 1 Then

    For p = 0 To 50

    If Array1(p) = 50000 Then

    p = p + 50

    Else

    'Cells(l - 1, q) = Array1(p)

    s = InStr(Array1(p), "-")

    'Cells(l - 1, q + 20) = s

    If s > 0 Then

    Array2() = Split(Array1(p), "-")

    Cells(l - 1, 520) = Array2(0)
    Cells(l - 1, 521) = Array2(1)

    u = Array2(0)

    For t = Array2(0) To Array2(1)

    Cells(l - 1, q) = u

    u = u + 1

    If u <= Array2(1) Then

    q = q + 1

    End If

    Next t

    Else

    Cells(l - 1, q) = Array1(p)

    End If

    End If

    q = q + 1

    Next p

    End If

    m = InStr(a, "switchport trunk allowed vlan ")
    If m = 1 Then a = Replace(a, "switchport trunk allowed vlan ", "")
    If m = 1 Then a = Replace(a, ",", ";")
    'If m = 1 Then a = Replace(a, "-", "99990")
    If m = 1 Then a = a & ";50000"
    n = Len(a)
    n = n - 1
    If m = 1 Then Cells(l, 100) = n
    If m = 1 Then Cells(l, 5).Value = a
    If m = 1 Then l = l + 1
    If m = 1 Then Array1() = Split(a, ";")
    If m = 1 Then

    For r = 0 To 50

    If Array1(r) = 50000 Then  (Cursor was on this line, when i pressed f8, it gave me the error)

    r = r + 50

    Else

    Cells(l - 1, q) = Array1(r)

    s = InStr(Array1(r), "-")

    If s > 0 Then

    Array2() = Split(Array1(r), "-")

    Cells(l - 1, 520) = Array2(0)
    Cells(l - 1, 521) = Array2(1)

    End If

    End If

    q = q + 1

    Next r

    End If

    Next i

    End Sub


     


    Atif Hafeez Network Specialist Design Engineer
    Friday, November 11, 2011 8:38 AM
  • You are comparing string (Array1) with long (50000) so you have type mis-match error. String and long are different types.

    In your example for r = 4, Array1(r) = "2078-2480" and this throws an error. With r = 1 to 3 VBA converts both values to double and does the comparison, but it can not convert value with "-" in it as it is not numeric.

    You can compare two strings, so this

    If Array1(r) = "50000" Then

    will not cause type mis-match error as it is equivalent to if "2078-2480" = "50000" then

     

    In general it is not a very good idea to allow excel to automatically convert data as it can lead to unexpected errors. Try to always compare the same data types or use Type conversion functions so that the conversion is explicit.

    • Marked as answer by Atif Hafeez Friday, November 11, 2011 9:27 AM
    • Edited by Abcadlo Friday, November 11, 2011 9:27 AM
    Friday, November 11, 2011 9:05 AM