Error 13: Type Mismatch Row Limit Restriction?

Question

When the excel sheet raw data has under 10,000 rows it runs, when it has 10,000 rows and over I get the error. Any idea? The error is pointed to the mu = Cells(joker, 12)

Columns("A:I").Select
Selection.ClearContents
Windows("New Registrations.xls").Activate
ActiveWindow.WindowState = xlNormal
Columns("A:I").Select
Selection.Copy
Windows("Polk Trend Report CYTD.xlsm").Activate
Range("A1").Select
ActiveSheet.Paste
Selection.Interior.ColorIndex = xlNone
Selection.Font.ColorIndex = 0

Sheets("Data").Select

Dim nz As Long
Dim joker As Long
Dim lambda As Long

nz = Cells(4, 12).Value

Dim mu As Long
    For joker = 5 To nz + 4
    lambda = Cells(joker, 11)
    mu = Cells(joker, 12)
        If lambda <> 0 And mu - lambda > 1 Then
            Range("A" & lambda).Select
            Selection.Copy
            Range("A" & lambda + 1 & ":A" & mu - 1).Select
            ActiveSheet.Paste
            Else:
        End If
Next joker

Range("N5:O" & nz + 4).Select
Selection.ClearContents

Dim iota As Long
Dim kappa As Long
iota = 7
Do While Cells(iota, 2).Value <> ""
    If Cells(iota, 2) = "UNKNOWN" Then
        kappa = Application.WorksheetFunction.Match(Cells(iota, 1).Value, Range("J1:J" & nz + 4), 0)
        Cells(kappa, 14).Value = Cells(iota, 7).Value
        Cells(kappa, 15).Value = Cells(iota, 5).Value
        Range("A" & iota & ":I" & iota).Select
        Selection.Delete Shift:=xlUp
        iota = iota - 1
    ElseIf Cells(iota, 2) = "Zone Total" Then
        Range("A" & iota & ":I" & iota).Select
        Selection.Delete Shift:=xlUp
        iota = iota - 1
    ElseIf Application.WorksheetFunction.And(Cells(iota, 5) = 0, Cells(iota, 7) = 0) Then
        Range("A" & iota & ":I" & iota).Select
        Selection.Delete Shift:=xlUp
        iota = iota - 1
    Else:
    End If
iota = iota + 1
Loop

Range("A" & iota & ":I" & iota).Select
Selection.Delete Shift:=xlUp

Range("C5:I5").Select
Selection.Copy
Range("C6").Select
ActiveSheet.Paste

Set pvtTable = Worksheets("Total Dealer (Trend)").Range("O5").PivotTable
pvtTable.RefreshTable
Sheets("Total Dealer (Trend)").Select
Cells.Select
Selection.Columns.AutoFit

Sheets("Data").Select
Range("S40:T" & nz + 39).Select
Selection.Copy
Range("A2").Select
Sheets("Total Dealer (Trend)").Select
Range("B40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False

Sheets("Data").Select
Range("U40:U" & nz + 39).Select
Selection.Copy
Range("A2").Select
Sheets("Total Dealer (Trend)").Select
Range("E40").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
Range("B40:E" & nz + 39).Select
Selection.Sort Key1:=Range("E40"), Order1:=xlDescending, Header:=xlNo _
    , OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
Range("A1").Select
ActiveWindow.WindowState = xlMaximized

End Sub

I'll take a look, but check this link out to [Avoid Using Select Statements](http://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba-macros) — peege, Dec 10 '14 at 23:20
What are you trying to accomplish, or shall I ask, "what does this effectively DO for 10,000 rows before it stops?" — peege, Dec 10 '14 at 23:26
The Macro is grabbing the raw data from another worksheet and formatting the data into separate tabs based on certain criteria in the data. The data compounds as the year progresses, so this is the first time it has stopped. I played around and found that if I deleted just a few rows to get under 10k rows it works, 10k or more i get the error and have no idea why. thanks for any insight — Shawn Ryan, Dec 10 '14 at 23:44
There is nothing unique after the 10k row in the excel sheet that on the surface I can see as being a format change.. Is there way i can set mu to equal a higher cell amount than 10k? — Shawn Ryan, Dec 10 '14 at 23:54
Are you familiar with the "Locals" window Shawn? it will tell you the values of all the variables at any breakpoint. — peege, Dec 11 '14 at 02:35
I ran a Locals Window and Debuged - I got the following: nz 30 long; joker 5 long; lambda 7 long; mu 0 long; iota 0 long; kappa 0 long; pvtTable Empty Variant/Empty — Shawn Ryan, Dec 11 '14 at 03:15
If that's the case, it's not making it anywhere near 10,000 when the error occurs. — peege, Dec 11 '14 at 04:23
If you used a file sharing site and post the link in your post. I could download it and see what's up from there. — peege, Dec 11 '14 at 04:34
Last night, I noticed in Cell k4 there was a populated number of 10000... I changed that number to 20000 and it ran.. thanks for everyone looking into this...i really appreciate it — Shawn Ryan, Dec 11 '14 at 14:06
Shawn, feel free to post an answer and accept it, so no one else is drawn into working on it, and/or has to read all the comments to know that. — peege, Dec 11 '14 at 20:12

score 0 · Answer 1 · answered Dec 10 '14 at 23:49

I think you might have a format change taking place on your Worksheet after row 10,000. Say it was a date, now it's General or some other type conflict, and you are getting a data mismatch as a result of the Value of mu being set by "L10000"

Check the format of the cells below 10,000. Especially Column "L"

Mark Moore · Answer 2 · 2014-12-11T01:32:56.817

0

this is an example error handler, hopefully you can just copy and paste this into your code as described and it should output the value of the failing cell when the error occurs, and then you can hopefully correct it. The following goes right at the top of your code

On Error GoTo MyProcedure_Error

Then the below goes above the end sub

MyProcedure_Exit:
      On Error GoTo 0
      Exit Sub

MyProcedure_Error:
       Select Case Err.Number
              'the "Case 9" statement below is left as an example to show how you could code a
              'specific error message if a specifc module needed it
              'Case 9
                  'MsgBox "The input file does not appear to be in the correct format, for importing into the " & _
                  '" Locations tab" & vbCrLf & "The expected format is " & Str(Import_Cols) & " columns, Pipe Delimited" & _
                  'vbCrLf, vbCritical, "Error in in procedure TrimColumn of Module DeveloperToolKit"
              Case Else
               MsgBox "An unexpected error has occured, the call value that has failed is." & _
                  vbCrLf & Cells(joker, 12) & _
                  vbCrLf & "Error Code = " & Str$(Err.Number) & _
                  vbCrLf & "Error Text = " & Err.Description, vbCritical, "Critical Error"
       End Select
       Resume MyProcedure_Exit

edited Dec 11 '14 at 01:32

answered Dec 11 '14 at 00:53

Mark Moore

520
4
13

Type Mismatch the code line is mu = Cells(joker, 12) – Shawn Ryan Dec 11 '14 at 01:03
OK, I see now why the previous comments mention "type" conflicts. Have you tried either adding an error and printing out "Cells(joker, 12)" in your error block? Either by debug.print or msgbox Cells(joker, 12) – Mark Moore Dec 11 '14 at 01:07
sorry that is meant to say "adding an error handler" – Mark Moore Dec 11 '14 at 01:08
Mark, thanks for your replies, i would have no idea on how to do either – Shawn Ryan Dec 11 '14 at 01:18
PS, exuse my poor spelling, its very late (or early) here. Off to bed now, good luck and will check back tomorrow :) – Mark Moore Dec 11 '14 at 01:34
Sorry, one last comment, you could alter the error message to put out any useful information such as the value of joker, to identify the row thats failing etc – Mark Moore Dec 11 '14 at 01:37
Thanks Mark.. I added the code and it said: Compile Error Duplicate Declaration in current scope. When I click OK it takes me to the Dim Joker As Long – Shawn Ryan Dec 11 '14 at 01:56
Hi Sean, sorry, I seem to be sending you backwards here. A good practice though that will help with this is to add option explict to the top of the code to enforce declarations, and then put all your declarations at the very top of the code, that should make it easy to spot your duplicate declaration – Mark Moore Dec 11 '14 at 22:29

Error 13: Type Mismatch Row Limit Restriction?

2 Answers2