Type issue

The macro doesn't do much. No type error when I ran it.

Post a workbook that generates the error to get help.

Kevin

I am not reproducing the problem when I run the macro. Does the sample workbook need data for the error to appear?

Also, you probably want to move the macro from ThisWorkbook to a regular module sheet as in the attached version of your workbook. ThisWorkbook and the worksheet code panes are class modules, and should be used by the event subs they contain rather than general purpose macros.

2A.xlsm

I recommend you declare your integer variables as Long rather than Integer. Long is a "long integer" while Integer is a "short integer" though both variable types occupy the same amount of memory. An Integer variable will overflow if you try to give it a value more than 32,767. This causes a runtime error if you are looping though the rows on a large worksheet. Rather than think about whether an overflow might occur, I just declare all integer variables as Long--and recommend you do so as well.

See attached error. No need other input file.

1l.png

To add to what Brad stated, we recommend putting general routines in general code modules - right-click anywhere in the project tree and select Insert->Code Module.

ThisWorkbook and worksheet modules are class modules but they are persistent classes which means an instance of that class object is instantiated at all times and any properties, methods (Subs), and functions are readily accessible by prefacing the routine name with "ThisWorkbook." or "Sheet1." Once running, the code will run exactly as it would in a general code module.

Note that any routines defined in the ThisWorkbook or a sheet module will not be exposed as a macro when using the Macros dialog. But you can refer to it in a command button's (or any shape's) OnAction property by prefacing the routine name with "ThisWorkbook."

Kevin

Peter,

Posting a picture of the error does not help us. We need a workbook that we can use to reproduce the error. Or show us the line of code that is encountering the error.

Kevin

Peter Chan,
I'm running the Beta Channel version of Microsoft 365. This is the latest version of Excel that has been released outside Microsoft. I do not get the error you show, so you must be running a different version of Excel.

I did encounter a Type 6 overflow error when I put 40,000 rows of data in your workbook. The fix for that is to change the Integer declarations to Long.

Brad

Workbook that I was testing. Variables were declared as Long.

2A.xlsm

I suspect it's a data dependent error.

These lines:

        P0 = InStr(Item0, "-"): P1 = Len(Item0)
        If P0 > 0 Then
            N0 = CInt(Mid(Item0, 1, P0 - 1)): N1 = CInt(Mid(Item0, P0 + 11, P1 - P0))

Will produce a type error if P0 or the result of the Mid function is not text that can be converted into a number.

Again, sample data that reproduces the error would help us help you.

Kevin

I just tested the workbook in Excel 2013, and could not reproduce the "Type Mismatch" error there, either.

Please post a workbook that reproduces the error. Tell us exactly which version of Excel you are using. Ideally, click the Debug choice when you get the error and tell us which statement is having problems and what the input values are for it.

Hi,
Can you re-try with file below?
https://1drv.ms/x/s!Ai8CrEskdewXvxEFAry_Mw8TFFCj?e=EVezuM

ErrorFilter.xlsm

I changed the Integer variables to Long, the CInt function to CLng, and the +11 to +1 in

            N0 = CLng(Mid(Item0, 1, P0 - 1)): N1 = CLng(Mid(Item0, P0 + 1, P1 - P0))

Select allOpen in new window

I changed it per your advice but it is same issue to this file.
https://1drv.ms/x/s!Ai8CrEskdewXvxOVJi9aEhTUEuWK?e=wgijJM

I also moved your sub to a regular module sheet.

For debugging purposes, I added a statement On Error GoTo Errhandler. And at the bottom of the sub, Errhandler said Resume--and I put a watch on it so the code would stop there while I investigated what was happening. The Type Mismatch error was occurring on column C 1800-22 and an overflow on 750-2058247. The Type Mismatch occurred when you tried to return two characters from beyond the right end of "1800-22." The overflow occurred because 2058247 is too big to fit in an integer variable

This version of the file removes my debugging statements, but includes the Long and CLng fixes.

ErrorFilter.xlsm

I reproduced your error and fixed it. I still don't like keeping the code in ThisWorkbook, but left it there.

Your second file was modified with variable declarations and changing 11 to 1. Both changes are commented in the code.

ErrorFilter-2.xlsm

To overflow issue, do you see my current file is changed to have Long variable instead? Why does error happen?