Type Mismatch: Formula with Range.

L

Legacy 108787

Guest
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 by a moderator:

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
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))"
 
Upvote 0
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 by a moderator:
Upvote 0
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))"
 
Upvote 0
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
 
Upvote 0
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))"
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

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
Back
Top