Link to home
Start Free TrialLog in
Avatar of Peter Chan
Peter ChanFlag for Hong Kong

asked on

Text on cell

Hi,
There is text in the beginning of the cell like
User generated imagehow to refer to the text, as I cannot show such text value using cstr to convert the cell value?
Avatar of Professor J
Professor J

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
Avatar of Peter Chan

ASKER

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?
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.
No way to achieve this using programming way?
Using VBA codes?
And I really expect to achieve this automatically.
SOLUTION
Avatar of Professor J
Professor J

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
The cell is having such format
User generated imageinside, 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

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
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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
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
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
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
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
User generated imagewithin 2nd sheet.
Would you please

 Debug.Print Range2.NumberFormat

and say what print in immediate window?

-FA
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
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

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

I mean the debug message and also I still see INR within 2nd Worksheet.
HuaMinChen

please open the attached and now press the refresh workbook button and it will work now.
t5.xlsm
I run your current Excel file but then I still have got "INR" within 2nd Worksheet.
can you write what is the actual correct name of the 2nd Sheet? i assume it is Budget-Management-AreaOffice  isn't it?
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
Yes, 2nd Worksheet is "Budget-Management-AreaOffice", I take your file and adjust one cell like
User generated image
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
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial