# USD to CADollar error

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

## 1 Answer

Change the line:

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

to:

``````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