Implementing Excel formula in VBA

0
votes

I have this Excel formula which I would like to implement via VBA, unfortunately it keeps yielding an error.

My Excel formula:

      IF(isemptyornothing(A1:A50); ""; IF(ISNUMBER(VALUE(A1:A50)); VALUE(A1:A50); A1:A50))

My VBA attempt:

    Range("B1:B50").Formula = _
  "=IF(IsEmptyorNothing(A1:A50), "", IF(ISNUMBER(VALUE(A1:A50)), VALUE(A1:A50), A1:A50))"

This yields the following error:

"Run-time error '1004': Application-defined or object-defined error"

Being a novice in VBA, I am not sure what to do with this.

1
vba

1 Answers

0
votes

"" needs to be """"

Also the formula you show would need to be array entered, but you can simple do this:

Range("B1:B50").Formula = _
  "=IF(IsEmptyorNothing(A1), """", IF(ISNUMBER(VALUE(A1)), VALUE(A1), A1))"

And Excel will put adjust the A1 as the formula is copied down, automatically.

This assumes that IsEmptyorNothing is a UDF, because no such formula exists in standard Excel.