Convert a cell or multiple cells from SGD value to USD value based on relevant monthly exchange rate

[ameenuksg]

Hi I want to create a VBA to convert 1 or multiple cells in Sheet 1 to the appropriate value by selecting them in column A and applying the relevant monthly exchange rate from Sheet 2.

Would greatly appreciate any help

Sheet 1	Sheet 1	Sheet 2	Sheet 2
A	B	A	B
Net Sales - $	Sales Date	Feb 2020	0.7543
200	3 Mar 2020	Mar 2020	0.7489
300	4 Apr 2020	Apr 2020	0.7123

 

[mse330]

Excellent A 20-day full curfew as just started in Kuwait ! … Stay safe

 

[ameenuksg]

Just want to improve the code to only convert rows of cells that are visible on the sheet after filtering a column or columns as the current code changes rows that were hidden or filtered too

 

[mse330]

Hey ameenuksg,

Try to replace the 2 in the below line with 12

For Each c In Rg.SpecialCells(2) '<--- Change it to 12

Just to give an explatation of the difference, the SpecialCells is very useful & could also be used without VBA in normal excel which can be accessed by either pressing F5 or Ctrl+G then clicking Special. In the code, the 2 refers to xlCellTypeConstants where I changed it now to 12 which refers to xlCellTypeVisible as you can see in the below picture. I encourage you to click HERE to understand more about it

 

[ameenuksg]

Thank you so much, thats really kind of you?

 

[ameenuksg]

Hi again, I see that the macro would not work on cells with values resulted from a formula. Can we tweak the vba to also convert cells with formula ??

 

[mse330]

Hi ameenuksg,

Did you change the number from 2 to 12 as per my post #23 ? Because if you did, it should consider all selected visible cells even if the cell value is a result of a formula

For Each c In Rg.SpecialCells(2) '<--- Change it to 12

 

[ameenuksg]

ok got it two birds with one stone ?