Cost Value Calculator

[3d4d5c]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Cost value (Local currency)[/TD]
[TD]Currency[/TD]
[TD]Cost value[/TD]
[/TR]
[TR]
[TD]500[/TD]
[TD]SGD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3664[/TD]
[TD]EUR[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

So I'm stuck in a situation whereby, I'm supposed to convert the cost value (local currency) into cost (in USD).
I had made a button to run the VBA Macro to calculate the values and paste back into the table.

Here's my coding:

Sub Costvalue()
Dim Country As String, Cost As Double, total As Double, xrate As Double
Dim r As Integer, c As Integer

r = 9
c = 10

'Cell(9,10) is at value 500.'

Country = ActiveCell(r, c)

Do While Country <> ""

If Country = "SGD" Then
xrate = 0.74
ElseIf Country = "EUR" Then
xrate = 1.13
ElseIf Country = "CHF" Then
xrate = 1
ElseIf Country = "GBP" Then
xrate = 1.29
End If

Cost = ActiveCell(r, c - 1).Value
Range(r, c + 1).Value = Cost * xrate 'here is the error'

r = r + 1
c = c + 1

Loop
End Sub

 

[Michael M]

Maybe this....UNTESTED
Sub Costvalue()
Dim Country As String, Cost As Double, total As Double, xrate As Double
Dim r As Integer, c As Integer
r = 9
c = 10
'Cell(9,10) is at value 500.'
Country = Cells(r, c)
Do While Country <> ""
    If Country = "SGD" Then
    xrate = 0.74
    ElseIf Country = "EUR" Then
    xrate = 1.13
    ElseIf Country = "CHF" Then
    xrate = 1
    ElseIf Country = "GBP" Then
    xrate = 1.29
    End If
    Cost = Cells(r, c - 1).Value
    Cells(r, c + 1).Value = Cost * xrate 'here is the error'
r = r + 1
c = c + 1
Loop
End Sub

 

[3d4d5c]

Alright! I managed to change your code abit. Thanks. (Y)

 

[Michael M]

Glad to help...