VBA - Avoid repeating an IF formula for each cell and apply to whole column?

[Fra88]

Hi, thanks a lot in advance for the help

I have a simple macro working for the below table:

A B C D
[TABLE="width: 500"]
<tbody>[TR]
[TD]Transport[/TD]
[TD]Amount[/TD]
[TD]Currency[/TD]
[TD]USD[/TD]
[/TR]
[TR]
[TD]Flight to Narita[/TD]
[TD]300[/TD]
[TD]USD[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Shinkanzen[/TD]
[TD]13,080[/TD]
[TD]YEN[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


If Currency in column "C" is YEN, the macro convert the amount in USD in column "D", otherwise, if the currency is "USD", the macro just copy the value from B to D.
What I did, however, was a very simple macro. Instead of repeating the lines each time as I did (first verifying if the value for C1 is yen or usd; then verifying C2; etc.), is there an easy

way to automatize this process or have the "if" condition apply to the whole column?

Thanks a lot !

Sub test()
 
If Sheets("Data").Range("C2").Value = "USD" Then
        Sheets("Data").Range("D2").Value = Sheets("Data").Range("B2").Value
ElseIf Sheets("Data").Range("C2").Value = "YEN" Then
        Sheets("Data").Range("D2").Value = Sheets("Data").Range("B2").Value*0.0089
Else: Sheets("Data").Range("C2").Value = "currency not USD nor YEN”
End If
 
If Sheets("Data").Range("C3").Value = "USD" Then
        Sheets("Data").Range("D3").Value = Sheets("Data").Range("B3").Value
ElseIf Sheets("Data").Range("C3").Value = "YEN" Then
        Sheets("Data").Range("D3").Value = Sheets("Data").Range("B3").Value*0.0089
Else: Sheets("Data").Range("C3").Value = "currency not USD nor YEN”
End If
 
End Sub

 

[Fluff]

How about

Sub Convert()
   With Range("D2", Range("C" & Rows.Count).End(xlUp).Offset(, 1))
      .Value = Evaluate(Replace(Replace("If(@c=""USD"",@b,if(@c=""YEN"",@b*0.0089,""currency not USD nor YEN""))", "@b", .Offset(, -2).Address), "@c", .Offset(, -1).Address))
   End With
End Sub

 

[Fra88]

You are amazing !
When you have time, can you help me understand a little what does your code mean?
It works perfectly, but I would not be able to replicate it at all

Thank you a lot !!!

 

[Fluff]

the code basically replicates a "standard" formula
in D2 =IF(C2="USD",B2,IF(C2="YEN",B2*0.0089,"currency not USD nor YEN"))
but using the entire Col range rather than just C2, B2
The @b & @c are just "place holders" & the replace function replaces them .Offset(, -2).Address & .Offset(, -1).Address respectively.

HTH