I am trying to develop a macro that converts USD to CAD. So far, the macro converts the value of the active cell to CAD while displaying the initial formula (required).

However, I get an error when the active cell contains the name of another cell in its formula.

For example:

  • If A2 is set to =32*4+16 when the function is called, the cell value becomes 149.27 and returns =32*4+16*USDCAD.

  • If A2 = 32*4+E7, calling the function will yield a cell error #NAME? and returns =32*4+'E7'*USDCAD

How can I convert the 'E7' cell to a value before/ while calling the USDCAD function?

Here is my code:

Sub ConvertToCAD()
    Dim LValue As String
    Dim aCell As Range
    Dim xRate As Double
    Range("USDCAD") = FXRate("USD", "CAD", "close")
    For Each aCell In Selection
        aCell.FormulaR1C1 = CStr(aCell.Formula) & "* USDCAD"
    Next aCell
End Sub
  • Aside: just curious, are you interested in (32*4+16)*USDCAD or is the 32*4 part already in CAD? – fideli Nov 26 '15 at 20:06

Change the line:

aCell.FormulaR1C1 = CStr(aCell.Formula) & "* USDCAD"


aCell.Formula = aCell.Formula & "* USDCAD"

On the LHS, you're interested in modifying the existing formula, not specifying a formula in R1C1 format. On the RHS, don't convert the existing formula to a string.

As you could have a series of terms in your USD calculation prior to converting, you may benefit from including the entire existing formula in parentheses, and then multiplying by the exchange rate, like so:

aCell.Formula = "(" & aCell.Formula & ") * USDCAD"

Thanks to Rob G for the above code snippet.

  • ...and assuming your earlier comment is correct, it might be better as aCell.Formula = "(" & aCell.Formula & ") * USDCAD" so it converts the entire formula rather than just multiplying part of it. – Rob Gale Nov 27 '15 at 2:34
  • Agreed. I'll change it as that's a much more general solution. – fideli Nov 27 '15 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.