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

ameenuksg

Board Regular
Joined
Jul 11, 2017
Messages
79
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 1Sheet 1Sheet 2Sheet 2
ABAB
Net Sales - $Sales DateFeb 20200.7543
2003 Mar 2020Mar 20200.7489
3004 Apr 2020Apr 20200.7123
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Ok, then try the below revised code

VBA Code:
Sub ApplyRate_V2()

Dim a, Rg As Range, c As Range
a = Sheets("Sheet2").[A1].CurrentRegion '<--- change sheet name as needed
Set Rg = Application.InputBox("Select your range", Type:=8) ' Comment this line if you want to make your range fixed
'Set Rg = sheets("Sheet1").range("S1:AB1000") 'Uncomment this line if you comment above line

With CreateObject("scripting.dictionary")
    For x = 3 To UBound(a) '<--- 3 is the 3rd row where your first number of data starts in sheet2
        If Not .exists(a(x, 1)) Then .Add WorksheetFunction.EoMonth(a(x, 1), 0), a(x, 2)
    Next x

    For Each c In Rg.SpecialCells(2)
        If IsNumeric(c.Value) And IsDate(Cells(c.Row, "F").Value) Then
            c.Value = c.Value * .Item(WorksheetFunction.EoMonth(Cells(c.Row, "F").Value, 0))
        End If
    Next
End With

End Sub
 
Upvote 0
Although I chose a range once it prompted, but it changed all the numbers in columns B - E & S - AC to 0
 
Upvote 0
Is there a period for those ranges in sheet 2 ? A rate for the same month ?
 
Upvote 0
Here is a screenshot of sheet 2
Sheet 2 named as Ex rate.JPG
 
Upvote 0
Ok, just noticed that if you select a single cell it would loop through all cells. Give the below a try

VBA Code:
Sub ApplyRate_V2()

Dim a, Rg As Range, c As Range
a = Sheets("Sheet2").[A1].CurrentRegion '<--- change sheet name as needed
Set Rg = Application.InputBox("Select your range", Type:=8) ' Comment this line if you want to make your range fixed
'Set Rg = sheets("Sheet1").range("S1:AB1000") 'Uncomment this line if you comment above line

With CreateObject("scripting.dictionary")
    For x = 3 To UBound(a) '<--- 3 is the 3rd row where your first number of data starts in sheet2
        If Not .exists(a(x, 1)) Then .Add WorksheetFunction.EoMonth(a(x, 1), 0), a(x, 2)
    Next x

    If Rg.Count = 1 And IsNumeric(Rg.Value) And IsDate(Cells(Rg.Row, "F").Value) Then
        Rg.Value = Rg.Value * .Item(WorksheetFunction.EoMonth(Cells(Rg.Row, "F").Value, 0))
    Else
        For Each c In Rg.SpecialCells(2)
             If IsNumeric(c.Value) And IsDate(Cells(c.Row, "F").Value) Then
                c.Value = c.Value * .Item(WorksheetFunction.EoMonth(Cells(c.Row, "F").Value, 0))
            End If
        Next
    End If
End With

End Sub
 
Upvote 0
its working wonders now?? one last request? once it changes, it adds four additional decimals like this 2233.206558, can help to adjust VBA to shorten to two decimals instead
 
Upvote 0
Glad it is working fine now :) … I have added Round function in the code as shown below

VBA Code:
Sub ApplyRate_V2()

Dim a, Rg As Range, c As Range
a = Sheets("Sheet2").[A1].CurrentRegion '<--- change sheet name as needed
Set Rg = Application.InputBox("Select your range", Type:=8) ' Comment this line if you want to make your range fixed
'Set Rg = sheets("Sheet1").range("S1:AB1000") 'Uncomment this line if you comment above line

With CreateObject("scripting.dictionary")
    For x = 3 To UBound(a) '<--- 3 is the 3rd row where your first number of data starts in sheet2
        If Not .exists(a(x, 1)) Then .Add WorksheetFunction.EoMonth(a(x, 1), 0), a(x, 2)
    Next x

    If Rg.Count = 1 And IsNumeric(Rg.Value) And IsDate(Cells(Rg.Row, "F").Value) Then
        Rg.Value = Round(Rg.Value * .Item(WorksheetFunction.EoMonth(Cells(Rg.Row, "F").Value, 0)), 2)
    Else
        For Each c In Rg.SpecialCells(2)
             If IsNumeric(c.Value) And IsDate(Cells(c.Row, "F").Value) Then
                c.Value = Round(c.Value * .Item(WorksheetFunction.EoMonth(Cells(c.Row, "F").Value, 0)), 2)
            End If
        Next
    End If
End With

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top