Write Formula in VBA

[ayazgreat]

Hi All

How should I write this formula in VBA ?

=SUMPRODUCT(--(FREQUENCY(L7:L & lr,L7:L & lr)>0))

Thanks in advance

 

[GlennUK]

Do you want to evaluate that in VBA or do you want to write that into a cell from VBA?

 

[Andrew Poulsom]

Do you want to put the formula in a cell? If so record a macro while entering it manually and you will get some code.

Or do you want to assign the result to a variable in VBA? If so you can use Evaluate like this:

Result = Evaluate("=SUMPRODUCT(--(FREQUENCY(L7:L" & LR & ",L7:L" & LR & ")>0))")

 

[ayazgreat]

I want to write that into a cell from VBA in Col K

 

[Andrew Poulsom]

Like this:

Activecell.Formula = "=SUMPRODUCT(--(FREQUENCY(L7:L" & LR & ",L7:L" & LR & ")>0))"

 

[ayazgreat]

Andrew Poulsom thanks, I want in Col K

 

[ayazgreat]

You mean to say

Range("B" & LR + 1).Formula = "=SUMPRODUCT(--(FREQUENCY(L7:L" & LR & ",L7:L" & LR & ")>0))"