Using Excel VBA to Find, Convert and Replace Currency in a String

This has quit a few bugs that i need to work on but it more or less does what i need it to do

Sub Curr_Convert()

Dim Cnvrtin_Rate As Single
Dim No_of_Char As Double
Dim Val_2_Cnvrt As Double
Dim Val_aftr_Cnvrt As Double

Dim Uncnvrtd_Strng As String
Dim cnvrtd_Strng As String
Dim cnvrtd_Strng2 As String

Dim cl As Range
Dim rng As Range

Cnvrtin_Rate = 80
'This will loop all cells in range A1:A10
' Modify the next line to use a different range
Set rng = Range("A1:A300")


For Each cl In rng.Cells
If Not cl.Value = vbNullString And Not IsError(cl.Value) Then

'String that need to be converted
Uncnvrtd_Strng = cl.Value

No_of_Char = (InStr((InStr(1, Uncnvrtd_Strng, "Tk. ") + 4), Uncnvrtd_Strng, " ")) - (InStr(1, Uncnvrtd_Strng, "Tk. ") + 4)

' Extracting the Currency that will be converted from the string
Val_2_Cnvrt = Mid(Uncnvrtd_Strng, (InStr(1, Uncnvrtd_Strng, "Tk. ") + 4), No_of_Char)

'Value of the converted Currency
Val_aftr_Cnvrt = Val_2_Cnvrt / Cnvrtin_Rate

'Replacing the Currency Value in the old string
cnvrtd_Strng = Replace(Uncnvrtd_Strng, Val_2_Cnvrt, Val_aftr_Cnvrt)

'Replacing the Currecy symbol
cnvrtd_Strng2 = Replace(cnvrtd_Strng, "Tk. ", "USD ", 1, 1)

'
cl.Value = cnvrtd_Strng2

End If

Next
End Sub

Example is

Cell A1 contains following string

the Company has reported consolidated profit after tax (excluding non-controlling interests) of Tk. 436.71 million with consolidated EPS of Tk. 0.83

After running my macro, this is what i get in Cell A1

the Company has reported consolidated profit after tax (excluding non-controlling interests) of USD 5.458875 million with consolidated EPS of Tk. 0.83

Now i'm stuck with few things, 1 being that i cant get it to run until all of the currency is converted to USD.

Thanks to David for some pointers, but anymore help on this is appreciated

You can use regular expressions to match patterns within a string. In this case, the "pattern" is:

"Tk\. \d*\.\d{2}"

This means that we're trying to find all substrings that start with "Tk." followed by a space, and numeric decimal with 2 decimal places. This can be modified but currently will match any length number whether 0.02 or 12345.67, etc.

Requires reference to Microsoft VBScript Regular Expressions 5.5

Assumes that the conversion rate/multiplier is in Range("B1") -- for my test I used value of 0.0129155 from XE.com

Sub fixcurrency()
    Dim numValue As String
    Dim replacement As String
    Dim fullReplacement As String
    Dim cl As Range
    Dim rng As Range
    Dim conversionRate As Double
    Dim regexp As regexp
    Dim allMatches As MatchCollection
    Dim m As Match

    'This will loop all cells in range A1:A10
    ' Modify the next line to use a different range
    Set rng = Range("A1:A10")

    'Assumes the conversion rate in range("B1")
    conversionRate = Range("B1")


    'Create our RegExp engine
    Set regexp = New regexp

    regexp.Global = True
    regexp.IgnoreCase = False
    regexp.Pattern = "Tk\. \d*\.\d{2}"

    For Each cl In rng.Cells
        fullReplacement = cl.Value

        Set allMatches = regexp.Execute(cl.Value)
        For Each m In allMatches

            'Remove the "tk. " from the cell's value & trim any leading/trailing spaces
            numValue = Trim(Replace(m.Value, "Tk.", " "))

            'Display the numeric value to ensure it is working correctly.
            ' once confirmed, remove this line:
            'MsgBox numValue

            'Compute the new value: multiply the numValue by the conversion rate, round to 2 decimals.
            replacement = "USD " & Round((CDbl(numValue) * conversionRate), 4)

            fullReplacement = Replace(fullReplacement, m.Value, replacement)
        Next
        'replace the cell's text with the replacement text
        cl.Value = fullReplacement
    Next
End Sub

Using your example data and the conversion rate for Bangladeshi Taka to USD (current as of this morning: http://www.xe.com/currencyconverter/convert/?From=BDT&To=USD), I get output like:

As per un-audited half yearly accounts as on 30.06.2014 (Jan'14 to June'14), the Company has reported consolidated net profit after tax (excluding non-controlling interests) of USD 0.0758 million with consolidated EPS of USD 0.0006 as against USD 0.4322 million and USD 0.004 respectively for the same period of the previous year. Whereas consolidated net profit after tax (excluding non-controlling interests) was USD 0.035 million with consolidated EPS of USD 0.0003 for the period of 3 months (Apr'14 to June'14) ended on 30.06.2014 as against USD 0.0958 million and USD 0.0009 respectively for the same period of the previous year.

And:

the Company has reported consolidated profit after tax (excluding non-controlling interests) of USD 5.6403 million with consolidated EPS of USD 0.0107