Type Mismatch: Formula with Range.

[Legacy 108787]

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:

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.

 

[Richard Schollar]

Hi

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

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

 

[Legacy 108787]

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.

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))"

 

[RoryA]

You could use:

   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]

Too slow

 

[Legacy 108787]

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.

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]

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))"

 

[Legacy 108787]

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!