Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 4 of 4

Thread: Cstr function

  1. #1
    Join Date
    9th August 2004
    Posts
    126

    Cstr function

    hi everybody
    i am trying to convert a range of numbers to text
    using the Cstr function ,it doesnt work.
    excel VBA help states that CStr function converts a numeric value to a String.
    i am aware that i can convert a number by preceding it with an apostrophe
    i just need to know why Cstr doesnt do the job

    VB:
    Sub covrt() 
        For Each rng In Selection.Cells 
            rng = CStr(rng.Value) 
        Next 
    End Sub 
    
    
    thanx in advance

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    7th March 2003
    Location
    Essex, England
    Posts
    11,313

    Re: Cstr function

    Hi,

    The CSTR() function is working but the range object is helpfully converting your numeric value back into a real value instead of text because thats what Excel likes
    So you need to tell it that it should be text and not a number.
    VB:
    Sub covrt() 
        Dim strValue As String 
        Dim rng As Range 
         
        For Each rng In Selection.Cells 
            strValue = CStr(rng.Value) 
            rng.NumberFormat = "@" 
            rng.value = strValue 
        Next 
    End Sub 
    
    

    Cheers
    Andy


  3. #3
    Join Date
    9th August 2004
    Posts
    126

    Re: Cstr function

    thanx very much andy for this
    now i should keep in mind something new
    not only the logic behind excel but also
    what excel likes

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    2nd November 2005
    Location
    Wessex
    Posts
    1,267

    Re: Cstr function

    Wouldn't it be easier just to change the format

    VB:
     
    Selection.NumberFormat = "@" 
    
    
    HTH

    Bob

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Possible Answers

  1. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:33
  2. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:32
  3. Date Function - NOW Function - Excel 2007 NOW Function
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: July 10th, 2008, 02:31
  4. XL97: How to Display Custom Function in Function Wizard Step 2
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 14:15
  5. XL2000: Cannot See User-Defined Function in the Paste Function Dialog ...
    By Dave Hawley in forum Excel Formulas (No Questions)
    Replies: 0
    Last Post: April 11th, 2008, 14:15

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
porno