Answered by:
Mismatch error
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,33723383", 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
Answers

You are comparing string (Array1) with long (50000) so you have type mismatch error. String and long are different types.
In your example for r = 4, Array1(r) = "20782480" 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 mismatch error as it is equivalent to if "20782480" = "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
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 semiccolons and splitting  it worked fine
When you tried to do the same for 401,3336,33723383. It would have split as
401
3336
33723383
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 
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,33723383. It should split as
401
3336
33723383
after doing this, i am just assigning each value in different cell. That is all. So, why it is giving mismatch error?
Atif Hafeez Network Specialist Design Engineer 

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,33723383. It should split as
401
3336
33723383
after doing this, i am just assigning each value in different cell. That is all. So, why it is giving mismatch error?
Atif Hafeez Network Specialist Design Engineer
HiCan 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 

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 
Here is the entire code. (error line is highlighted)
Input:
interface GigabitEthernet1/1 description link to ISAMOlaya HJ (11400000) switchport switchport trunk encapsulation dot1q switchport trunk allowed vlan 99,1359,1599,2077,20782480 switchport mode trunk switchport nonegotiate dampening 2 250 2000 10 udld port disable wrrqueue bandwidth percent 27 35 38 wrrqueue randomdetect minthreshold 1 30 60 90 100 100 100 100 100 wrrqueue randomdetect maxthreshold 1 100 100 100 100 100 100 100 100 no wrrqueue randomdetect 2 no wrrqueue randomdetect 3 stormcontrol broadcast level 0.50 stormcontrol multicast level 0.50 no cdp enable spanningtree bpdufilter enable Code:
Sub test1()
'Define an integer i for making a loop to select a range of cells.
Dim i As IntegerDim 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 + 1k = 0
k = InStr(a, "interface TenGigabitEthernet")
If k = 1 Then Cells(l, 4).Value = a
If k = 1 Then l = l + 1m = 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 ThenFor 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 ThenFor 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 
You are comparing string (Array1) with long (50000) so you have type mismatch error. String and long are different types.
In your example for r = 4, Array1(r) = "20782480" 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 mismatch error as it is equivalent to if "20782480" = "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