Search code examples
excelvbaif-statementexcel-formulaisnumeric

Using IsNumeric Function in a Formula

I was wondering if anyone had any suggestions for what I'm doing wrong, or if it's even possible.

I am working with 3 columns: Column X = # of Services, Column Y = # of Bills, and Column Z which is a calculation of X divided by Y.

Sometimes, there is a text entry in Column X - when that happens, I want Column Z to simply reflect what Column Y contains.

Here is the code I've written... I've tried (seemingly) all possible combinations of quotation marks and parentheses, to no avail.

ActiveCell.FormulaR1C1 = _
    "=IF(IsNumeric(RC[-2]),=RC[-2]/RC[-1],=RC[-1])"

Thank you!

Solution

  • Your formula is wrong, use this:

    ActiveCell.FormulaR1C1 = "=IF(IsNumber(RC[-2]),RC[-2]*RC[-1],RC[-1])"

    Note that IsNumeric is a VBA function and its equivalent in Excel functions is IsNumber. Sometimes functions have different names for Excel and VBA, this is one of the cases.