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 becomes149.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
(32*4+16)*USDCAD
or is the32*4
part already in CAD?