Text on cell

Hi,
There is text in the beginning of the cell like
j.pnghow to refer to the text, as I cannot show such text value using cstr to convert the cell value?
LVL 12
HuaMin ChenProblem resolverAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Professor JMicrosoft Excel ExpertCommented:
That is not text , it is just how the cells are formatted using one of the currencies . So these are just the appearance of cell and cells do not include such text as values. To change it back to normal or different format . Select cells then right click and select format cells from the right click menu then on the first tab select General or any other type of format and click ok
HuaMin ChenProblem resolverAuthor Commented:
Hi,
Using these
    Dim Sheet0 As Worksheet
    Dim Find0 As Variant
    Dim Replace0 As Variant
    
    Find0 = "INR"
    Replace0 = "CNY"
    
    For Each Sheet0 In ActiveWorkbook.Worksheets
      Sheet0.Cells.Replace what:=Find0, Replacement:=Replace0, _
        LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
        SearchFormat:=False, ReplaceFormat:=False
    Next Sheet0

Open in new window

I'm not able to replace that specific code with another code. Is there a way to use VBA to achieve this?
Professor JMicrosoft Excel ExpertCommented:
As I mentioned earlier out he cells appear not having any text INR , becuSe that is just a format not value, so Yiur vba code above cannot fix the formatting because there is no actual INR text as value in those cells.
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

HuaMin ChenProblem resolverAuthor Commented:
No way to achieve this using programming way?
HuaMin ChenProblem resolverAuthor Commented:
Using VBA codes?
HuaMin ChenProblem resolverAuthor Commented:
And I really expect to achieve this automatically.
Professor JMicrosoft Excel ExpertCommented:
it is possible and very easy

try below code

you can change the used range to selection if you want


Sub test()
Dim cl As Range

For Each cl In ActiveSheet.UsedRange

If cl.NumberFormat = "[$INR] #,##0.00" Then
cl.NumberFormat = "[$CNY] #,##0.00"
End If
Next cl
End Sub

Open in new window

HuaMin ChenProblem resolverAuthor Commented:
The cell is having such format
m.pnginside, but I cannot change that using these

    Dim Range2 As Range
    
    For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange
    
        If Range2.NumberFormat = "INR 0.00" Then
            Range2.NumberFormat = "CNY 0.00"
        End If
    Next Range2

Open in new window

HuaMin ChenProblem resolverAuthor Commented:
It is also not fine by these
    Dim Range2 As Range, Format0 As String
    
    For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange
    
        'If Range2.NumberFormat = "INR 0.00" Then
        Format0 = Range2.NumberFormat
        If InStr(Format0, "INR") > 0 Then
            Format0 = Replace(Format0, "INR", "CNY")
            Range2.NumberFormat = Format0
        End If
    Next Range2

Open in new window

Farzad AkbarnejadDeveloperCommented:
Hi,
Number Format must be "[$INR] #,##0.00"  not "INR 0.00" in your code. Also "CNY 0.00" to "[$CNY] #,##0.00"

-FA
HuaMin ChenProblem resolverAuthor Commented:
I try with these

    Dim Range2 As Range, Format0 As String
    
    For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange
    
        If Range2.NumberFormat = "[$INR] #,##0.00" Then
            Range2.NumberFormat = "[$CNY] #,##0.00"
        End If
    Next Range2

Open in new window


but it is not OK.
Farzad AkbarnejadDeveloperCommented:
Hi,
Would you please upload your excel file here. I can debug it,
I think that condition on For Each loop not be fired. Place break point on line 5 and 6. Maybe Range2 isn't pointed to correct range.

-FA
HuaMin ChenProblem resolverAuthor Commented:
Could you please refer to the attached?

You can press "Refresh workbook" button within 1st sheet, and the relevant codes do exist within "Check_Sitecode" module. Many thanks
t5.xlsm
Farzad AkbarnejadDeveloperCommented:
There isn't nothing errors. All currency changed to CNY in uploaded excel file.

-FA
HuaMin ChenProblem resolverAuthor Commented:
Sorry, the problem does exist within the sheet "Budget-Management-AreaOffice".
Farzad AkbarnejadDeveloperCommented:
And which buttons must be pressed in  "Budget-Management-AreaOffice" sheet to run code?

-FA
HuaMin ChenProblem resolverAuthor Commented:
When you press "Refresh Workbook" button within 1st sheet, it would also refresh sheet "Budget-Management-AreaOffice". Thanks a lot.
Farzad AkbarnejadDeveloperCommented:
Yes. OK. there isn't any error in it. Now all its currency changed to CNY. Which error message you get when run it?

-FA
HuaMin ChenProblem resolverAuthor Commented:
After I've pressed "Refresh ..." button, I still get "INR" within the sheet "Budget-Management-AreaOffice".
Farzad AkbarnejadDeveloperCommented:
And I don't see. You see CNY and INR mix or see only INR. Are you enable all macro security alert in your file? Also add
Debug.Print Range2.NumberFormat

Open in new window

after
For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange

Open in new window

before if condition in your loop and tell me which number format is printed fro each cell.

-FA
Professor JMicrosoft Excel ExpertCommented:
please use this code

it will work.

Sub ActualUsedRange()
  Dim cl As Range
On Error Resume Next
ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
On Error GoTo 0
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
    

For Each cl In Selection

If cl.NumberFormat = "[$INR] #,##0.00" Then
cl.NumberFormat = "[$CNY] #,##0.00"
End If
Next cl

End Sub

Open in new window


i have also put this code in your workbook attached.  you can run the macro by pressing control shift A
t5.xlsm
HuaMin ChenProblem resolverAuthor Commented:
I call the event like
Sub ActualUsedRange(Para_Message As String)
    Dim cl As Range
    On Error Resume Next
    ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
    On Error GoTo 0
      Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
          Cells.Find(What:="*", SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
        
    
    For Each cl In Selection
    
        If cl.NumberFormat = "[$INR] #,##0.00" Then
            cl.NumberFormat = "[$CNY] #,##0.00"
        End If
    Next cl

End Sub

    Message0 = ""
    ActualUsedRange Message0
    ...

Open in new window

but then I still have got many INR below
o.pngwithin 2nd sheet.
Farzad AkbarnejadDeveloperCommented:
Would you please

 Debug.Print Range2.NumberFormat

and say what print in immediate window?

-FA
Professor JMicrosoft Excel ExpertCommented:
why did you change my code.?  what is the error handling you put after the end of the End Sub?  what is the point for that?

did you open the attachment i uploaded? if you open that atachment and press Control + Shift+ A then you will see that it perfectly works. i tested it myself.
Farzad AkbarnejadDeveloperCommented:
And other try

change all NumberFormat keyword to  NumberFormatLocal.

-FA
Farzad AkbarnejadDeveloperCommented:
or change you locale of your computer to English US and test with NumberFormat.

-FA
Professor JMicrosoft Excel ExpertCommented:
again, i am reiterating what you are trying to achieve is done with simple macro this one.

please do not change it and run it and it will work.  you are passing another argument to the sub routine, which i do not see the point for that. why dont you just use the code below?

Sub ActualUsedRange()
  Dim cl As Range
On Error Resume Next
ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
On Error GoTo 0
  Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
      Cells.Find(What:="*", SearchOrder:=xlByColumns, _
      SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
    

For Each cl In Selection

If cl.NumberFormat = "[$INR] #,##0.00" Then
cl.NumberFormat = "[$CNY] #,##0.00"
End If
Next cl

End Sub

Open in new window

HuaMin ChenProblem resolverAuthor Commented:
Many thanks all.

FA,
I put

Sub ActualUsedRange(Para_Message As String)
    Dim cl As Range
    On Error Resume Next
    ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
    On Error GoTo 0
      Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
          Cells.Find(What:="*", SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select
    
    For Each cl In Selection
    Debug.Print c1.NumberFormat
        If cl.NumberFormatLocal = "[$INR] #,##0.00" Then
            cl.NumberFormatLocal = "[$CNY] #,##0.00"
        End If
    Next cl

End Sub

Open in new window


but I get nothing shown, after having pressed "Refresh Workbook" button.
HuaMin ChenProblem resolverAuthor Commented:
I call the event running these codes

Sub ActualUsedRange(Para_Message As String)
    Dim cl As Range
    On Error Resume Next
    ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
    On Error GoTo 0
    Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
          Cells.Find(What:="*", SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select

    For Each cl In Selection
    
        If cl.NumberFormat = "[$INR] #,##0.00" Then
            cl.NumberFormat = "[$CNY] #,##0.00"
        End If
    Next cl

End Sub

Open in new window

but there're still INR within 2nd worksheet.
Professor JMicrosoft Excel ExpertCommented:
Why you are giving it a argument of Para_Message?
This spoils the code I gave you if you want to enforce running. The ma to everytime something changes the go to that specific sheet module and select worksheet change event and the call ActualUsedRange from there

What you have asked I answered your question with a working code , if you need more modification I suggest you open a new question.
HuaMin ChenProblem resolverAuthor Commented:
JimJam,
I now run these

Sub ActualUsedRange()
    Dim cl As Range
    On Error Resume Next
    ThisWorkbook.Sheets("Budget-Management-AreaOffice").Activate
    On Error GoTo 0
    Range("A1").Resize(Cells.Find(What:="*", SearchOrder:=xlRows, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Row, _
          Cells.Find(What:="*", SearchOrder:=xlByColumns, _
          SearchDirection:=xlPrevious, LookIn:=xlValues).Column).Select

    For Each cl In Selection
    
        If cl.NumberFormat = "[$INR] #,##0.00" Then
            cl.NumberFormat = "[$CNY] #,##0.00"
        End If
    Next cl

End Sub

Open in new window

but really INR are still inside Sheet Budget-Management-AreaOffice, after having pressed "Refresh Workbook" button.
Farzad AkbarnejadDeveloperCommented:
After running debug.Print commands what you get in immediate windows? (Press Ctrl+G to open immediate windows)
HuaMin ChenProblem resolverAuthor Commented:
And I don't see. You see CNY and INR mix or see only INR. Are you enable all macro security alert in your file? Also add
Debug.Print Range2.NumberFormat

                                         
1:
Select all
Open in new window
after
For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange

                                         
1:
Select all
Open in new window
before if condition in your loop and tell me which number format is printed fro each cell.

-FA

FA,

I have got nothing shown, due to these codes

    Dim Range2 As Range, Format0 As String
    
    For Each Range2 In Worksheets("Budget-Management-AreaOffice").UsedRange
Debug.Print Range2.NumberFormat
        If Range2.NumberFormat = "[$INR] #,##0.00" Then
            Range2.NumberFormat = "[$CNY] #,##0.00"
        End If
    Next Range2

Open in new window

HuaMin ChenProblem resolverAuthor Commented:
I mean the debug message and also I still see INR within 2nd Worksheet.
Professor JMicrosoft Excel ExpertCommented:
HuaMinChen

please open the attached and now press the refresh workbook button and it will work now.
t5.xlsm
HuaMin ChenProblem resolverAuthor Commented:
I run your current Excel file but then I still have got "INR" within 2nd Worksheet.
Professor JMicrosoft Excel ExpertCommented:
can you write what is the actual correct name of the 2nd Sheet? i assume it is Budget-Management-AreaOffice  isn't it?
Professor JMicrosoft Excel ExpertCommented:
i have now run the macro and then uploaded the file after macro was ran.

can you post a screenshot where do you see INR in the worksheet Budget-Management-AreaOffice?

i do not see any in the attached.
t5.xlsm
HuaMin ChenProblem resolverAuthor Commented:
Yes, 2nd Worksheet is "Budget-Management-AreaOffice", I take your file and adjust one cell like
q.png
in above, and then I run "Refresh ..." button within 1st Sheet, but I really get no change to that cell. Please refer to the Excel file as attached.
t6.xlsm
Professor JMicrosoft Excel ExpertCommented:
ok the reason it was not affecting that particular cell is becuase sometimes the usedrange property is not the ture used range. now i have put some codes and try running it and it will work in the attached version.
t6.xlsm
aikimarkCommented:
I think you want the .Text property of the cell.  That will show the formatted cell contents.  You can do what you need to with the results.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.