Automatic Currency Exchange Macro

[S.J. Powell]

Need someones help!

This is an example of what I have in Excel:

Project Name Value Currency
Proj1 100 US Dollar
Proj2 50 Canadian Dollar
Proj3 510 Canadian Dollar

I want a macro to convert just the Canadian labled projects to U
dollars. The converted values need to replace the old values in th
table.

If my conversion rate was 0.5, my new data set should look like:

Project Name Value Currency
Proj1 100 US Dollar
Proj2 25 Canadian Dollar
Proj3 255 Canadian Dollar

Any suggestions????:confused

 

[Frank Kabel]

Hi
one way:
sub foo()
Dim lastrow as long
Dim rowindex as long
dim currency_factor as double

currency_factor = 0.5
lastrow = activesheet.Cells(Rows.Count, "C").End(xlUp).Row
For rowindex = 2 to lastrow
if cells(rowindex,"C").value = "Canadian Dollar" then
cells(rowindex,"C").value = "US Dollar"
cells(rowindex,"B").value = cells(rowindex,"B").value *
currency_factor
end if
next
end sub

 

[Bob Phillips]

For i = 1 To Cells(Rows.Count,"C").End(xlUp).Row
If Cells(i,"C").Value = "Canadian Dollar" Then
Cells(i,"B").Value = Cells(i,"B").Value * 0.5
End If
Next i

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Bob
i would also change the currency label for theses cells to 'US Dollars'

 

[Bob Phillips]

Hi Frank,

Yeah, so would I but the OP's example didn't (sic!).

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Frank Kabel]

Hi Frank,

Yeah, so would I but the OP's example didn't (sic!).

lol - o.k. you won
Frank