Runtime error 13 : type mismatch

C

Caveman1957

I am receiving the runtime error 13 message on
this line of code

If UCase(CUCM.Cells(CUCMSourceRow, 4))
UCase(ALIR.Cells(MainLoop, 15)) And UCase(CUCM.Cells(CUCMSourceRow, 2)
= UCase(ALIR.Cells(MainLoop, 14)) And (UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 11)) Or UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 12))) Then


the entire code this is from is

Sub Button1_Click()
Dim LastRow As Long
Dim LastResultRow As Long
Dim DestinationRow As Long
Dim ALIRSourceRow As Long
Dim CUCMSourceRow As Long
Dim MainLoop As Long

LastRow = ALIR.UsedRange.Rows.Count
DestinationRow = 3
ALIRSourceRow = 2
CUCMSourceRow = 2
MatchType = 0
LastResultRow = Results.UsedRange.Rows.Count
Results.Range(Cells(3, 1), Cells(LastRow, 9)).ClearContents
Results.Range(Cells(3, 1), Cells(LastRow, 9)).Interior.ColorIndex = 0

Do While CUCM.Cells(CUCMSourceRow, 3) <> ""
For MainLoop = 2 To LastRow
'If match all then MatchType = 1
If CUCM.Cells(CUCMSourceRow, 5) = Right(ALIR.Cells(MainLoop
32), 10) Then
MatchType = 3
ALIRSourceRow = MainLoop
'If UCase(CUCM.Cells(CUCMSourceRow, 4))
UCase(ALIR.Cells(MainLoop, 15)) Then
'If UCase(CUCM.Cells(CUCMSourceRow, 2))
UCase(ALIR.Cells(MainLoop, 14)) Then
'If UCase(CUCM.Cells(CUCMSourceRow, 1))
UCase(ALIR.Cells(MainLoop, 11)) Or UCase(CUCM.Cells(CUCMSourceRow, 1))
UCase(ALIR.Cells(MainLoop, 12)) Then
If UCase(CUCM.Cells(CUCMSourceRow, 4))
UCase(ALIR.Cells(MainLoop, 15)) And UCase(CUCM.Cells(CUCMSourceRow, 2)
= UCase(ALIR.Cells(MainLoop, 14)) And (UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 11)) Or UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 12))) Then
MatchType = 1
Call WriteRow(MainLoop, CUCMSourceRow, DestinationRow
MatchType)
DestinationRow = DestinationRow + 1
Exit For
Else
Call WriteRow(MainLoop, CUCMSourceRow, DestinationRow
MatchType)
DestinationRow = DestinationRow + 1
Exit For
End If
Else
'If UCase(CUCM.Cells(CUCMSourceRow, 4))
UCase(ALIR.Cells(MainLoop, 15)) Then
'If UCase(CUCM.Cells(CUCMSourceRow, 2))
UCase(ALIR.Cells(MainLoop, 14)) Then
'If UCase(CUCM.Cells(CUCMSourceRow, 1))
UCase(ALIR.Cells(MainLoop, 11)) Or UCase(CUCM.Cells(CUCMSourceRow, 1))
UCase(ALIR.Cells(MainLoop, 12)) Then
If UCase(CUCM.Cells(CUCMSourceRow, 4))
UCase(ALIR.Cells(MainLoop, 15)) And UCase(CUCM.Cells(CUCMSourceRow, 2)
= UCase(ALIR.Cells(MainLoop, 14)) And (UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 11)) Or UCase(CUCM.Cells(CUCMSourceRow
1)) = UCase(ALIR.Cells(MainLoop, 12))) Then
MatchType = 2
Call WriteRow(MainLoop, CUCMSourceRow, DestinationRow
MatchType)
DestinationRow = DestinationRow + 1
Exit For
End If
End If
Next MainLoop
CUCMSourceRow = CUCMSourceRow + 1

MatchType = 5
Loop

End Sub


Function WriteRow(ByVal ALIRSourceRow As Long, _
ByVal CUCMSourceRow As Long, _
ByVal DestinationRow As Long, _
ByVal MatchType As Integer)
Dim ALIRSourceCol(1 To 2) As Integer
Dim CUCMSourceCol(1 To 5) As Integer
Dim LoopCount As Integer
Dim GREEN As Integer
Dim YELLOW As Integer
Dim ORANGE As Integer
Dim BLUE As Integer

ALIRSourceCol(1) = 21 'Principal Country Location
ALIRSourceCol(2) = 28 'Unique Key
CUCMSourceCol(1) = 1 'First Name
CUCMSourceCol(2) = 2 'Last Name
CUCMSourceCol(3) = 3 'User ID
CUCMSourceCol(4) = 4 'Department


GREEN = 43
YELLOW = 6
ORANGE = 45
BLUE = 23


For LoopCount = 1 To 4
CUCM.Cells(CUCMSourceRow, CUCMSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, LoopCount).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

For LoopCount = 1 To 2
ALIR.Cells(ALIRSourceRow, ALIRSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, (LoopCount + 4)).PasteSpecia
(xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

Results.Cells(DestinationRow, 7).Value = MatchType
Results.Cells(DestinationRow, 8).Value = CUCMSourceRow
Results.Cells(DestinationRow, 9).Value = ALIRSourceRow

Select Case MatchType
Case 0
Case 1
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex
GREEN
Next LoopCount
Case 2
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex =
YELLOW
Next LoopCount
Case 3
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex =
ORANGE
Next LoopCount
Case 4
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex = BLUE
Next LoopCount
End Select
End Function


I cannot see why I am getting this error can anyone please help me?
 
C

Caveman1957

I have changed the code to eliminate the for-next loop and added a
error handler because i was getting 400.
now error is 'type mis-match' but I cannot see which line this occur
on.
:(


Sub Button1_Click()
Dim LastALIRRow As Long
Dim LastResultRow As Long
Dim DestinationRow As Long
Dim ALIRSourceRow As Long
Dim CUCMSourceRow As Long
Dim MainLoop As Long
Dim MatchType As Integer

On Error GoTo Errorcatch

'Get Last Row containing Data on ALIR Worksheet (Could be up to 400,00
rows)
LastALIRRow = ALIR.UsedRange.Rows.Count
'Initialise Variables before loop
DestinationRow = 3
ALIRSourceRow = 2
CUCMSourceRow = 2
MatchType = 5
MainLoop = 2
'Get Last Row containing Data on Results Worksheet
LastResultRow = Results.UsedRange.Rows.Count
'Clear Cells of Data on Results Worksheet
Results.Range(Cells(3, 1), Cells(LastResultRow, 9)).ClearContents
'Apply NOFill to Cells on Results Worksheet
Results.Range(Cells(3, 1), Cells(LastResultRow, 9)).Interior.ColorInde
= 0

Do While CUCM.Cells(CUCMSourceRow, 3) <> ""
Do While MatchType = 5 And MainLoop < LastALIRRow + 1
'Check if match for First and Last name and User ID
If UCase(CUCM.Cells(CUCMSourceRow, 4).Value)
UCase(ALIR.Cells(MainLoop, 15).Value) An
UCase(CUCM.Cells(CUCMSourceRow, 2).Value) = UCase(ALIR.Cells(MainLoop
14).Value) And (UCase(CUCM.Cells(CUCMSourceRow, 1).Value)
UCase(ALIR.Cells(MainLoop, 11).Value) Or UCase(CUCM.Cells(CUCMSourceRow
1).Value) = UCase(ALIR.Cells(MainLoop, 12).Value)) Then
'Check if match for Phone Number
If CUCM.Cells(CUCMSourceRow, 5).Value
Right(ALIR.Cells(MainLoop, 32).Value, 10) Then
MatchType = 1
ALIRSourceRow = MainLoop
Else
MatchType = 2
ALIRSourceRow = MainLoop
End If
Else
'Check if match for Phone Number
If CUCM.Cells(CUCMSourceRow, 5).Value
Right(ALIR.Cells(MainLoop, 32).Value, 10) Then
MatchType = 3
ALIRSourceRow = MainLoop
End If
End If
MainLoop = MainLoop + 1
Loop
If MatchType < 5 Then
Call WriteRow(ALIRSourceRow, CUCMSourceRow, DestinationRow
MatchType)
DestinationRow = DestinationRow + 1
End If
CUCMSourceRow = CUCMSourceRow + 1
MatchType = 5
MainLoop = 2
Loop

Exit Sub

Errorcatch:
MsgBox Err.Description

End Sub



Function WriteRow(ByVal ALIRSourceRow As Long, _
ByVal CUCMSourceRow As Long, _
ByVal DestinationRow As Long, _
ByVal MatchType As Integer)
Dim ALIRSourceCol(1 To 2) As Integer
Dim CUCMSourceCol(1 To 5) As Integer
Dim LoopCount As Integer
Dim GREEN As Integer
Dim YELLOW As Integer
Dim ORANGE As Integer
Dim BLUE As Integer

ALIRSourceCol(1) = 21 'Principal Country Location
ALIRSourceCol(2) = 28 'Unique Key
CUCMSourceCol(1) = 1 'First Name
CUCMSourceCol(2) = 2 'Last Name
CUCMSourceCol(3) = 3 'User ID
CUCMSourceCol(4) = 4 'Department


GREEN = 43
YELLOW = 6
ORANGE = 45
BLUE = 23


For LoopCount = 1 To 4
CUCM.Cells(CUCMSourceRow, CUCMSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, LoopCount).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

For LoopCount = 1 To 2
ALIR.Cells(ALIRSourceRow, ALIRSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, (LoopCount + 4)).PasteSpecia
(xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

Results.Cells(DestinationRow, 7).Value = MatchType
Results.Cells(DestinationRow, 8).Value = CUCMSourceRow
Results.Cells(DestinationRow, 9).Value = ALIRSourceRow

Select Case MatchType
Case 0
Case 1
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex
GREEN
Next LoopCount
Case 2
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex
YELLOW
Next LoopCount
Case 3
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex
ORANGE
Next LoopCount
Case 4
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex = BLUE
Next LoopCount
End Select
End Functio
 
C

Caveman1957

Caveman1957;1615808 said:
I have changed the code to eliminate the for-next loop and added a
error handler because i was getting 400.
now error is 'type mis-match' but I cannot see which line this occur
on.
:(
Click to expand...

The type mis-match was due to an error when the data was imported fro
text file to the ALIR worksheet. an '=' was prepended to certain dat
that started with a '-'.
I amended the error catch code to help find this.
The issue I have now is the length of time taken for the code t
execute.
With 10,000 rows in the CUCM worksheet being compared to 400,000 rows i
the ALIR worksheet to find matches office is being locked up while th
code executes not just excel. Excel shows 'not responding' in the windo
title bar.

Does anyone have an idea as to how i can made the coding run quicker?

the following code shows my modifications to help catch this dat
error.
Sub Button1_Click()
Dim LastALIRRow As Long
Dim LastResultRow As Long
Dim DestinationRow As Long
Dim ALIRSourceRow As Long
Dim CUCMSourceRow As Long
Dim MainLoop As Long
Dim MatchType As Integer

On Error GoTo Errorcatch

'Get Last Row containing Data on ALIR Worksheet (Could be up to 400,00
rows)
LastALIRRow = ALIR.UsedRange.Rows.Count
'Initialise Variables before loop
DestinationRow = 3
ALIRSourceRow = 2
CUCMSourceRow = 2
MatchType = 5
MainLoop = 2
'Get Last Row containing Data on Results Worksheet
LastResultRow = Results.UsedRange.Rows.Count
'Clear Cells of Data on Results Worksheet
Results.Range(Cells(3, 1), Cells(LastResultRow, 9)).ClearContents
'Apply NOFill to Cells on Results Worksheet
Results.Range(Cells(3, 1), Cells(LastResultRow, 9)).Interior.ColorInde
= 0

Do While CUCM.Cells(CUCMSourceRow, 3).Value <> ""
Do While MatchType = 5 And MainLoop < LastALIRRow + 1
'Check if match for First and Last name and User ID
If UCase(CUCM.Cells(CUCMSourceRow, 4).Value)
UCase(ALIR.Cells(MainLoop, 15).Value) An
UCase(CUCM.Cells(CUCMSourceRow, 2).Value) = UCase(ALIR.Cells(MainLoop
14).Value) And (UCase(CUCM.Cells(CUCMSourceRow, 1).Value)
UCase(ALIR.Cells(MainLoop, 11).Value) Or UCase(CUCM.Cells(CUCMSourceRow
1).Value) = UCase(ALIR.Cells(MainLoop, 12).Value)) Then
'Check if match for Phone Number
If CUCM.Cells(CUCMSourceRow, 5).Value
Right(ALIR.Cells(MainLoop, 34).Value, 10) Then
MatchType = 1
ALIRSourceRow = MainLoop
Else
MatchType = 2
ALIRSourceRow = MainLoop
End If
Else
'Check if match for Phone Number
If CUCM.Cells(CUCMSourceRow, 5).Value
Right(ALIR.Cells(MainLoop, 34).Value, 10) Then
MatchType = 3
ALIRSourceRow = MainLoop
End If
End If
MainLoop = MainLoop + 1
Loop
If MatchType < 5 Then
Call WriteRow(ALIRSourceRow, CUCMSourceRow, DestinationRow
MatchType)
DestinationRow = DestinationRow + 1
End If
CUCMSourceRow = CUCMSourceRow + 1
MatchType = 5
MainLoop = 2
Loop
CUCM.Cells(3, 11).T
Exit Sub

Errorcatch:
MsgBox Err.Description
MsgBox "CUCM Row " & CUCMSourceRow
MsgBox "ALIR Row " & ALIRSourceRow
MsgBox "Match Type " & MatchType
MsgBox "Loop Counter " & MainLoop


End Sub



Function WriteRow(ByVal ALIRSourceRow As Long, _
ByVal CUCMSourceRow As Long, _
ByVal DestinationRow As Long, _
ByVal MatchType As Integer)
Dim ALIRSourceCol(1 To 2) As Integer
Dim CUCMSourceCol(1 To 5) As Integer
Dim LoopCount As Integer
Dim GREEN As Integer
Dim YELLOW As Integer
Dim ORANGE As Integer
Dim BLUE As Integer

ALIRSourceCol(1) = 21 'Principal Country Location
ALIRSourceCol(2) = 28 'Unique Key
CUCMSourceCol(1) = 1 'First Name
CUCMSourceCol(2) = 2 'Last Name
CUCMSourceCol(3) = 3 'User ID
CUCMSourceCol(4) = 4 'Department


GREEN = 43
YELLOW = 6
ORANGE = 45
BLUE = 23


For LoopCount = 1 To 4
CUCM.Cells(CUCMSourceRow, CUCMSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, LoopCount).PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

For LoopCount = 1 To 2
ALIR.Cells(ALIRSourceRow, ALIRSourceCol(LoopCount)).Copy
Results.Cells(DestinationRow, (LoopCount + 4)).PasteSpecia
(xlPasteValues)
Application.CutCopyMode = False
Next LoopCount

Results.Cells(DestinationRow, 7).Value = MatchType
Results.Cells(DestinationRow, 8).Value = CUCMSourceRow
Results.Cells(DestinationRow, 9).Value = ALIRSourceRow

Select Case MatchType
Case 0
Case 1
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex =
GREEN
Next LoopCount
Case 2
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex =
YELLOW
Next LoopCount
Case 3
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex =
ORANGE
Next LoopCount
Case 4
For LoopCount = 1 To 7
Results.Cells(DestinationRow, LoopCount).Interior.ColorIndex = BLUE
Next LoopCount
End Select
End Function
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Shapes in 2010 1
type mismatch error in macro in excel 0
Help needed! Extracting Heading 3 and Contents from a Word Doc to Excel 0
Help with Macro please... 6
CheckIBAN 5
Delete rows based on results of cell tests 1
User defined data type (losing its data) 2
Opeing PDF's withing a MAcro I have written 0

Top