Converting Nested Excel Formula (SUM, IF, FREQUENCY) into VBA

[MrcAm]

I`m trying to move a formula that I already have working in Excel into VBA. I am trying to add the unique instances of numbers in column E together. The Excel formula is as follows:

=SUM(IF(FREQUENCY(E:E,E:E)>0,1))

I have tried playing around with the worksheetfunction but can`t seem to get it to work correctly. Can someone help me with this? Thank you in advance for trying.

 

[VoG]

I think that the easiest way to do this would be to use the formula and convert the results to values

With Range("A1")
    .Formula = "=SUM(IF(FREQUENCY(E:E,E:E)>0,1))"
    .Value = .Value
End With

Or take a look at the Evaluate method.

 

[MrcAm]

I think that the easiest way to do this would be to use the formula and convert the results to values

With Range("A1")
    .Formula = "=SUM(IF(FREQUENCY(E:E,E:E)>0,1))"
    .Value = .Value
End With

Or take a look at the Evaluate method.

The Evaluate method fits my needs perfectly. Thank you very much for the help.