How to loop columns to find cells formatted as currency, write currency in next col, and reformat

[Brutusar]

Hi,

Let me start with an example;

• A sheet has two columns, A and B.
• Col A can be formatted as currency
• Col B is formatted as text, without data.

There are many rows, and some of the cells will be formatted as currency, others as text. The reason is that data has been appended to the sheet from different sources.

How can I loop thru these columns and

1. Find cells in col A that are formatted as currency
2. When/if found the corresponding currency code should be written in to same row, but col B ( $ in col A should be USD in col B)
3. The cell in col A should be formatted as number with two decimals so the currency symbol does not show

 

[Coding4Fun]

That is going to be quite a challenge if not impossible given what you have posted. Being that it appears that the $ symbol is used to represent multiple currencies as shown here, how to know if it should be Tuvalu Dollar or United States Dollar and there are more that use $

 

[Coding4Fun]

It might be possible if you have a know set of currencies that you are working with and you can assume that $ is always going to be USD.

 

[Brutusar]

It will not always be the same currency I am afraid, it can be at least 20 different currencies.

 

[Coding4Fun]

So what you would need to do is build out your list of currencies and their corresponding values such as $ = USD £ = GBP etc.

Then you can use a for loop to loop through the data and populate the corresponding values.

This is example code that covers 2 currencies, $ which will populate USD, and £ which will populate GBP
You would need to add in all of the other currencies that you want to handle and assuming you dont have any overlap you should be good.

Sub IdentifyCurrency()

myLastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To myLastRow
checkVal = Range("A" & i).Text
Select Case True
    Case checkVal Like "$*"
        MsgBox "USD"
        Range("B" & i).Value = "USD"
    Case checkVal Like "£*"
        MsgBox "GBP"
        Range("B" & i).Value = "GBP"
End Select

Next i

End Sub

Will produce

 

[bebo021999]

With data in column A, from cell A1
Currency symbol suppose to 1 digit

Option Explicit
Sub test()
Dim lr&, cell As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lr)
    ' case of cell with manual input currency: "$200"
    If Not IsNumeric(cell) And IsNumeric(Right(cell, Len(cell) - 1)) Then
        cell.Offset(, 1).Value = Left(cell, 1)
        cell.Value = Right(cell, Len(cell) - 1)
    ' case of cell with currency format: 200 to displayed as $200
    ElseIf Len(cell.Text) <> Len(cell) Then
        cell.Offset(, 1).Value = Left(cell.Text, 1)
        cell.NumberFormat = "#,###.00"
    End If
Next
End Sub

 

[Coding4Fun]

With data in column A, from cell A1
Currency symbol suppose to 1 digit

Option Explicit
Sub test()
Dim lr&, cell As Range
lr = Cells(Rows.Count, "A").End(xlUp).Row
For Each cell In Range("A1:A" & lr)
    ' case of cell with manual input currency: "$200"
    If Not IsNumeric(cell) And IsNumeric(Right(cell, Len(cell) - 1)) Then
        cell.Offset(, 1).Value = Left(cell, 1)
        cell.Value = Right(cell, Len(cell) - 1)
    ' case of cell with currency format: 200 to displayed as $200
    ElseIf Len(cell.Text) <> Len(cell) Then
        cell.Offset(, 1).Value = Left(cell.Text, 1)
        cell.NumberFormat = "#,###.00"
    End If
Next
End Sub

The code I provided will address points 1 and 2. to loop through all the rows and populate column B based on the currency.

This code that bebo021999 provided will address point 3 which I overlooked, you should run his code after you run mine - Thanks bebo021999

 

[Coding4Fun]

Oops you can remove those message boxes too

MsgBox "USD"

 

[Brutusar]

Thanks guys, both macros do in principle work, but with a small hickup. When a sum is formatted as currency, the currency symbol can be after the sum as well as in front of the sum.
If a sum is in USD it will be like this: $1,200,00, but if it is in Euro it will be like this: 1.200,00 €.

 

[Coding4Fun]

Thanks guys, both macros do in principle work, but with a small hickup. When a sum is formatted as currency, the currency symbol can be after the sum as well as in front of the sum.
If a sum is in USD it will be like this: $1,200,00, but if it is in Euro it will be like this: 1.200,00 €.

Sub IdentifyCurrency()

myLastRow = Cells(Rows.Count, 1).End(xlUp).Row

For i = 2 To myLastRow
checkVal = Range("A" & i).Text
Select Case True
    Case checkVal Like "*$*"
        Range("B" & i).Value = "USD"
    Case checkVal Like "*£*"
        Range("B" & i).Value = "GBP"
End Select

Next i

End Sub