Type Mismatch: Formula with Range.

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
Hi!
I've been reading along on this forum for the past few weeks, and I've always found answers to my questions. Great community! :)
This time though, I couldn't find a solution, so here I am...

I have the following code:
Code:
Dim MyRange As Range
    Set MyRange = Range("Data!B8", Range("Data!B65536").End(xlUp))
    MyRange.Select
    Sheets("Analyse").Select
    Range("C7").Select
    Selection.FormulaArray = "=SUM(IF(MONTH(" & MyRange & ")=RC[-2],1,0))"
Now I get a Runtime Error 13: Type Mismatch on the last line I posted in the code.

I don't understand this, as the MyRange is definitely filled. The MyRange.Select shows the correct colums selected. Then why does this give a type mismatch? The MyRange selection has nothing but numeric dates in it, formatted as such (not accidentally formatted as text).

I'm using Excel 2003 SP3, on Windows XP SP2.
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Richard Schollar

MrExcel MVP
Joined
Apr 19, 2005
Messages
23,707
Hi

You need the address of the MyRange range object so you just need to access the Address property:

Rich (BB code):
Selection.FormulaArray = "=SUM(IF(MONTH(" & MyRange.Address & ")=RC[-2],1,0))"
 

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
Wow, that was quick!

I added the .Address, but though that solves the runtime error 13, I now catch a Runtime error 1004: Unable to set the FormulaArray property of the Range class. Still on the same line.

Code:
Dim MyRange As Range
    Sheets("Data").Select
    Set MyRange = Range("B8", Range("B65536").End(xlUp))
    MyRange.Select
    Sheets("Analyse").Select
    Range("C7").Select
    Selection.FormulaArray = "=SUM(IF(MONTH(" & MyRange.Address & ")=RC[-2],1,0))"
 
Last edited:

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,146
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You could use:
Code:
   Dim MyRange As Range
   With Sheets("Data")
      Set MyRange = .Range("B8", .Cells(.Rows.Count, "B").End(xlUp))
   End With
    Sheets("Analyse").Range("C7").FormulaArray = "=SUM(IF(MONTH(Data!" & _
            MyRange.Address(ReferenceStyle:=xlR1C1) & ")=RC[-2],1,0))"
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061

ADVERTISEMENT

Too slow
 

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
That does indeed work... but I don't understand how :P

I took the liberty of changing it to this, as I didn't understand most of your edits.
Code:
Dim MyRange As Range
    Sheets("Data").Select
    Set MyRange = Range("B8", Range("B65536").End(xlUp))
    MyRange.Select
    Sheets("Analyse").Select
    Range("C7").Select
    Selection.FormulaArray = "=SUM(IF(MONTH(Data!" & _
          MyRange.Address(ReferenceStyle:=xlR1C1) & ")=RC[-2],1,0))"

Could you explain to me what this:
=SUM(IF(MONTH(Data!" & MyRange.Address(ReferenceStyle:=xlR1C1) & ")=RC[-2],1,0))"
does? And why does "Data!" has to be put before the MyRange again? Isn't that declared in the range itself, before?
I have to re-use the code later and like to understand what I'm doing.

Lots of love to you, people! <3
 

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
The problem was that the code was mixing R1C1 style range references with A1 style..

MyRange.Address returns in A1 style A1:A100 etc...
But the other part of the formula used R1C1 style RC[-2]

Using MyRange.Address(ReferenceStyle:=xlR1C1) changes A1:A100 to R1C1:R100C1 (or whatever)

You could also do it like this, change the RC[-2] to A7

Sheets("Analyse").Range("C7").FormulaArray = "=SUM(IF(MONTH(Data!" & _
MyRange.Address & ")=A7,1,0))"
 

Skunk

Board Regular
Joined
Jan 5, 2009
Messages
83
The problem was that the code was mixing R1C1 style range references with A1 style..

MyRange.Address returns in A1 style A1:A100 etc...
But the other part of the formula used R1C1 style RC[-2]

Using MyRange.Address(ReferenceStyle:=xlR1C1) changes A1:A100 to R1C1:R100C1 (or whatever)

You could also do it like this, change the RC[-2] to A7

Sheets("Analyse").Range("C7").FormulaArray = "=SUM(IF(MONTH(Data!" & _
MyRange.Address & ")=A7,1,0))"

Ah, okay! I get it now.
Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,173
Messages
5,600,133
Members
414,365
Latest member
UUR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top