Help Needed in VBA

[go2rishu]

Hi, I have this requireemnt. I have to extract the currency Symbol, say USD or AED etc. from a cell and show it in another cell.


I tried assigning the result of ActiveCell.NumberFormat in to a String and then extracting only the first part of it till "]" using a While-Loop.


But it seems that, "_([$USD" is waht I get and the most difficult part is _ is a charecter but "($USD" all together acts like a SINGLe charecter. hence I'm not able to remove it.


Please let me know is there a bette way of extracting this Info.

 

[go2rishu]

This is the Code I've used


Dim cform, curform As String

cform = ""

cform = amt.NumberFormat 'amt is the source cell

i = 1

curform = ""

While Mid(cform, i, 1) <> "]"

curform = curform & Mid(cform, i, 1)

i = i + 1

Wend

 

[NARAYANK991]

Hi go2rishu ,


There are two ways to set the Currency symbol in your worksheet.


One way is to use the Regional and Language Options , in the Control Panel , where you can customize the currency symbol to USD. When you do this , and enter an amount like , say , 37.53 in cell C5 , and select the Currency format for that cell , the cell value will be displayed as USD37.53 , without any space between the letter D and the digit 3. When you use the NumberFormat property to get the cell format , you get $#,##0.00


Another way is to leave the currency symbol in the Regional and Language Options to the default for your region , say $ , and format the worksheet cells to the desired format. In this case , you can select the Currency format for cell C5 and choose the USD symbol from the alphabetical list in the drop-down list box. Now , the cell value will be displayed as USD 37.53 , with a space between the letter D and the digit 3. When you use the NumberFormat property to get the cell format , you get [$USD] #,##0.00


Retrieving the currency symbol in the second case is done by using the =MID function as follows :


=MID(C5,3,3)


which will give you USD or AED or any other desired currency symbol.


Are you getting the same result when you do this ? Please let me know.


Narayan