Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Runtime error 13 : type mismatch
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)).PasteSpecial (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? |
#2
|
|||
|
|||
I have changed the code to eliminate the for-next loop and added an error handler because i was getting 400.
now error is 'type mis-match' but I cannot see which line this occurs 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,000 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.ColorIndex = 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) And 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)).PasteSpecial (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 |
#3
|
|||
|
|||
Quote:
I amended the error catch code to help find this. The issue I have now is the length of time taken for the code to execute. With 10,000 rows in the CUCM worksheet being compared to 400,000 rows in the ALIR worksheet to find matches office is being locked up while the code executes not just excel. Excel shows 'not responding' in the window 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 data 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,000 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.ColorIndex = 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) And 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)).PasteSpecial (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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Runtime Error 13 Type Mismatch | Excel Programming | |||
Runtime Error Type 13 Mismatch | Excel Programming | |||
Runtime Error '13': Type mismatch | Excel Programming | |||
Runtime error 13 type mismatch ? | Excel Programming | |||
Runtime error 13 type mismatch ? | Excel Programming |