Excel formula error on VBA

Question

I am trying to put the following code into VBA. What I ideally want is to apply the formula to the column H when column A is empty. Any help would be appreciated. endPosition is a variable which holds the row# value where column A is empty.

ActiveCell.Formula = "=SUM(N(FREQUENCY(R[endPosition]C[-1]:R[endPosition]C[-1],R[endPosition]C[-1]:R[endPosition]C[-1])>0))"

This formula is applied at the end when the loop sees a blank cell:

    Range("A1").Select
    beginPosition = 2 'Start from row number 2
    Do While IsEmpty(ActiveCell.Offset(1, 0)) = False
    ActiveCell.Offset(1, 0).Select
    endPosition = ActiveCell.Row
    Loop
    endPosition = endPosition + 1
    Range("H15").Select
    ActiveCell.Formula = "=SUM(N(FREQUENCY(R[endPosition]C[-1]:R[endPosition]C[-1],R[endPosition]C[-1]:R[endPosition]C[-1])>0))"

I know that this is not the right way. But I am trying to learn Excel and hence the stupid formula. — rvphx
– rvphx, Commented Mar 27, 2012 at 14:52
The error that I get is "Run-time error '1004': Application-defined or object-defined error" — rvphx
– rvphx, Commented Mar 27, 2012 at 14:53

assylias · Accepted Answer · 2012-03-27 14:59:30Z

6

Assuming the formula is correct, if endPosition is a variable, you should not include it in the string:

ActiveCell.FormulaR1C1 = "=SUM(N(FREQUENCY(R[" & endPosition & _
     "]C[-1]:R[" & endPosition & "]C[-1],R[" & endPosition & _
     "]C[-1]:R[" & endPosition & "]C[-1])>0))"

answered Mar 27, 2012 at 14:59

assylias

330k84 gold badges680 silver badges806 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rvphx Over a year ago

That resolved the error. I will have the fix the positions for this formula to work correctly. Thanks!

Collectives™ on Stack Overflow

Excel formula error on VBA

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related