VBA to address multiple currecy

[gdgonzal]

Hello,

I have a macro below that separates currency from a custom format. It works fine but as you can see it only addresses CAN and I need to be able to add additional currency like the ones I have listed below. How can I modify this vba to accomplish this?

Need to add:
"#,##0" ZAR";-#,##0" ZAR";#,##0;@"
"#,##0" INR";-#,##0" INR";#,##0;@"
"" £"#,##0;" £"-#,##0;#,##0;@" INR";#,##0;@" (Note this is a prefix symbol while the others listed about are suffixes)



Sub SeparateCurrency()Dim cell As RangeDim ConvertRange As RangeDim LastRow As LongDim FormatString As String LastRow = Range("A65536").End(xlUp).Row Set ConvertRange = ActiveSheet.Range("A1:A" & LastRow) For Each cell In ConvertRange FormatString = cell.NumberFormat cell.Select If cell.NumberFormat = "#,##0"" CAD"";-#,##0"" CAD"";#,##0;@" Then cell.Offset(0, 1).Value = "CAD" cell.Offset(0, 2).Value = cell.Value End If Next End Sub
</PRE>

 

[taigovinda]

Welcome to the board! A couple of options might be to repeat the "if...end if" statement that you already have, and just create a new one for each format type, or you could use a Case Select statement as below.

Sub SeparateCurrency()
    Dim rngCel As Range
    Dim ConvertRange As Range
    Dim LastRow As Long
    Dim FormatString As String
    LastRow = Range("A65536").End(xlUp).Row
    Set ConvertRange = ActiveSheet.Range("A1:A" & LastRow)
    For Each rngCel In ConvertRange
        FormatString = rngCel.NumberFormat
        Select Case FormatString
            Case "#,##0"" CAD"";-#,##0"" CAD"";#,##0;@"
                rngCel.Offset(0, 1).Value = "CAD"
                rngCel.Offset(0, 2).Value = rngCel.Value
            Case "#,##0"" ZAR"";-#,##0"" ZAR"";#,##0;@"
                rngCel.Offset(0, 1).Value = "ZAR"
                rngCel.Offset(0, 2).Value = rngCel.Value
            Case "#,##0"" INR"";-#,##0"" INR"";#,##0;@"
                rngCel.Offset(0, 1).Value = "INR"
                rngCel.Offset(0, 2).Value = rngCel.Value
            Case """ £""#,##0;"" £""-#,##0;#,##0;@"" INR"";#,##0;@"
                rngCel.Offset(0, 1).Value = "£"
                rngCel.Offset(0, 2).Value = rngCel.Value
            Case Else
                rngCel.Offset(0, 1).Value = "Currency not specified"
                rngCel.Offset(0, 2).Value = rngCel.Value
        End Select
    Next
End Sub

Note that you can make your code easier to read by using code tags... Put this *after* your code:
[/code]
...and this before it:

Also note that the variable name "cell" is not used as it could cause you to get scolded by folks on this forum who know more than me - and it could potentially cause weird issues with the VBA too!