CODEWITHSUNDEEP
vbaexcel
Atlas91
Atlas91

Reputation: 5904

Run-time error '1004' using a formula in a macro vba

I'm trying to insert a formula in a macro vba and write the output in a cell but i get a run-time error.. I'm not a vba developer so i can't understand what is wrong. I write only some simple macros.. This is what i wrote so far:

Sub count()

  Range("E6").Formula = "=SUM(IF(FREQUENCY(MATCH(A2:A96;A2:A96;0);MATCH(A2:A96;A2:A96;0))>0;1))"

End Sub

I expect that in the E6 cell will get the output, using only the formula i get 51, but when i run the macro i get the error.. Any help? Thanks

EDIT: Using InputBox

 Dim myRange As Range

    Set myRange = Application.InputBox(Prompt:= _
        "Select a Range", _
        Title:="InputBox Method", Type:=8)

    Range("E6").Formula = "=SUM(IF(FREQUENCY(MATCH(A2:A96,A2:A96,0),MATCH(A2:A96,A2:A96,0))>0,1))"

Upvotes: 0

Views: 2399

Answers (2)

Rory
Rory

Reputation: 34075

You should us US style separators:

  Range("E6").Formula = "=SUM(IF(FREQUENCY(MATCH(A2:A96,A2:A96,0),MATCH(A2:A96,A2:A96,0))>0,1))"

Upvotes: 2

L42
L42

Reputation: 19737

As commented, this should work:

Range("E6").Formula = _
    "=SUM(IF(FREQUENCY(MATCH(A2:A96,A2:A96,0),MATCH(A2:A96,A2:A96,0))>0,1))"

Upvotes: 0

Related Questions