Text on cell

HuaMin Chen
HuaMin Chen used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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 resolver

Author

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 Expert
Top Expert 2014

Commented:
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

HuaMin ChenProblem resolver

Author

Commented:
No way to achieve this using programming way?
HuaMin ChenProblem resolver

Author

Commented:
Using VBA codes?
HuaMin ChenProblem resolver

Author

Commented:
And I really expect to achieve this automatically.
Professor JMicrosoft Excel Expert
Top Expert 2014
Commented:
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 resolver

Author

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 resolver

Author

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

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 resolver

Author

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.
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 resolver

Author

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
There isn't nothing errors. All currency changed to CNY in uploaded excel file.

-FA
HuaMin ChenProblem resolver

Author

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

-FA
HuaMin ChenProblem resolver

Author

Commented:
When you press "Refresh Workbook" button within 1st sheet, it would also refresh sheet "Budget-Management-AreaOffice". Thanks a lot.
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 resolver

Author

Commented:
After I've pressed "Refresh ..." button, I still get "INR" within the sheet "Budget-Management-AreaOffice".
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 Expert
Top Expert 2014

Commented:
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 resolver

Author

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.
Would you please

 Debug.Print Range2.NumberFormat

and say what print in immediate window?

-FA
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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.
And other try

change all NumberFormat keyword to  NumberFormatLocal.

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

-FA
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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 resolver

Author

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 resolver

Author

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 Expert
Top Expert 2014

Commented:
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 resolver

Author

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.
After running debug.Print commands what you get in immediate windows? (Press Ctrl+G to open immediate windows)
HuaMin ChenProblem resolver

Author

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 resolver

Author

Commented:
I mean the debug message and also I still see INR within 2nd Worksheet.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
HuaMinChen

please open the attached and now press the refresh workbook button and it will work now.
t5.xlsm
HuaMin ChenProblem resolver

Author

Commented:
I run your current Excel file but then I still have got "INR" within 2nd Worksheet.
Professor JMicrosoft Excel Expert
Top Expert 2014

Commented:
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 Expert
Top Expert 2014

Commented:
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 resolver

Author

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 Expert
Top Expert 2014

Commented:
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
Top Expert 2014
Commented:
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.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial