SUMPRODUCT throwing RunTime Error 13 - Type mismatch

**Ochimus** · 10-03-2018, 05:10 PM

SUMPRODUCT Macro on attached using named Ranges generates RUNTIME ERROR 13 - Type Mismatch, and can't see why?

Need to enter into Col D the match in Q14 - X22 for two criteria - the string in Col D and the month in Q4.

Col D shows the correct answer using a formula.
Col E shows the correct answer using VBA but with "hardwired" ranges
This approach is no use, because the "ranges" will change each month.

Col F should show the answer using the Named Ranges, but it generates a RUNTIME error.

I have also tried approaching this with VBA Index Match and SUMIFS, but none delivered the answer.

Any solutions, suggestions or alternatives are welcome.

Ochimus

**¯\_(ツ)_/¯** · 10-03-2018, 05:48 PM

I think your two VBA formulas should be:

Please Login or Register  to view this content.

**¯\_(ツ)_/¯** · 10-03-2018, 06:02 PM

Also, not sure why you used "b - 1" to get the value for MATCHrng and VALUErng. Get rid of the merged cell and text that occurs in Q23:X23, and just use the variable b. You can then use the variable b in the first formula for Offset(0, 1). Full new code:

Please Login or Register  to view this content.

**Ochimus** · 10-03-2018, 07:53 PM

Many thanks for prompt corrections. After twenty years using Excel I really should have realised the omissions!

I added the merged cells for each range simply to make the structure crystal clear, after various people said in the past they found it difficult to follow the sequences.

And "selection.offset(1,0)" was added to omit the Headers for the ranges, and "b-1" to omit the Total lines.

Happily, can now mark this as "Solved"

Ochimus

**¯\_(ツ)_/¯** · 10-03-2018, 11:08 PM

Glad I could help!

SUMPRODUCT throwing RunTime Error 13 - Type mismatch

LinkBack

Thread Tools

Rate This Thread

Display

SUMPRODUCT throwing RunTime Error 13 - Type mismatch

Re: SUMPRODUCT throwing RunTime Error 13 - Type mismatch

Re: SUMPRODUCT throwing RunTime Error 13 - Type mismatch

Re: SUMPRODUCT throwing RunTime Error 13 - Type mismatch

Re: SUMPRODUCT throwing RunTime Error 13 - Type mismatch

Thread Information

Users Browsing this Thread

Similar Threads

Runtime Error 13 Type Mismatch

[SOLVED] Runtime error 13 Type mismatch

Type mismatch runtime error 13

Runtime Error Type Mismatch

[SOLVED] Runtime Error '13' Type Mismatch

runtime error '13' type mismatch

[SOLVED] Runtime Error Type 13 Mismatch

Bookmarks

Bookmarks

Posting Permissions