Sudden "Type mismatch error"

**EdwardStephenson** · 05-29-2014, 05:12 AM

Hi all

I've had a macro running on a shared spreadsheet for a few weeks now with no issues, but this morning it has decided to stop working and give me the "Run-time error '13': Type mismatch". I don't have much experience with macros and most of this macro was written by someone else so I don't know exactly what I'm doing.

I've only attached the sheet with the macro in it as the rest of the spreadsheet contains confidential data...

Here's what the macro is meant to do:

Every time a cell in column A updates (based on a gigantic nested IF formula) then
update column O with the value of column B
update column C with the value of column A
update column B with the current date

Debugging the macro shows it gets stuck at

Formula:

Please Login or Register  to view this content.

I hope this makes sense, thanks

**Norie** · 05-29-2014, 05:20 AM

Have you checked what's in the cells rngC and rngC.Offset(0,2) refer to?

**haripopuri** · 05-29-2014, 05:21 AM

Error is because your IF formula resulting into error in cell A212. Modify IF formula not to result in error. You may use another nested IF or IFERROR formula.

**EdwardStephenson** · 05-29-2014, 05:27 AM

Yes haripopuri!!

Thank you very much!

**haripopuri** · 05-29-2014, 05:56 AM

You are welcome. On a second look at your IF formula, why don't to use a table with possible values and apply a simple VLOOKUP or INDEX & MATCH formulas?

**EdwardStephenson** · 05-29-2014, 06:12 AM

But some of the possible values are ranges, for example (M6>=400,M6<=607). So how could I illustrate that in a table of possible values?

**EdwardStephenson** · 05-29-2014, 06:23 AM

Actually, I could just set the range lookup to true and include the range boundaries in the possible values table, couldn't I?

**haripopuri** · 05-29-2014, 10:21 AM

Nah, not that easy. Try the attachment if it works out for you.

**EdwardStephenson** · 05-29-2014, 10:43 AM

Woah! Thanks for putting so much time in!! I would give you extra reputation if I could.

I think I've managed to make it work though using vlookups... I simply include the lower bound (ignore upper bounds) for each "auto MI" value that has a range.

**haripopuri** · 05-29-2014, 10:49 AM

Perfect. Don't forget to keep the table sorted.

Sudden "Type mismatch error"

LinkBack

Thread Tools

Rate This Thread

Display

Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Re: Sudden "Type mismatch error"

Thread Information

Users Browsing this Thread

Similar Threads

Excel 2010 (Run-time error '13' type mismatch) "Debug" and "Continue" Grayed out.

[SOLVED] VBA Excel 2007 : Autofit Merge Cells Error "Run-time error '13': Type Mismatch"

Excel 2007: Error "13 - Type Mismatch" when assigning a value to a cell.

[SOLVED] Merged cells cause "Runtime error 13 type mismatch" problem

[SOLVED] Error Handling to mitigate "Run Time Erorr 13 Type Mismatch"

Bookmarks

Bookmarks

Posting Permissions