Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 7 of 7

Thread: To convert cell content into text using VBA

  1. #1
    Join Date
    19th October 2005
    Posts
    18

    To convert cell content into text using VBA

    Hi All,

    Can some correct my code. I want to convert my cell content to text. for example if i have column B with neumeric values (199234) i want to convert this to text and put only the text value (no formula after conversion) in the same cell.

    Below is my code, much appriciated if you can
    VB:
     
    Sub format() 
        Dim rows As Integer 
        Dim temp1, gtxt As String 
        Set sh = ActiveSheet 
        Set xrow = Range("a1:b1", Range("F1").End(xlDown)) 
        rows = xrow.rows.Count 
         
        For a = 2 To rows 
            temp1 = Cells(a, "B") 
            ActiveCell.Formula = "=Text(temp1, 0)" 
            ActiveCell.Copy 
            Cells(a, "B").PasteSpecial.Value 
             
        Next 
    End Sub 
    
    
    Thanks in advance.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,423

    Re: To convert cell content into text using VBA

    Are you trying to convert to values?
    VB:
    ActiveCell.Value=ActiveCell.Value 
    
    
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    19th October 2005
    Posts
    18

    Re: To convert cell content into text using VBA

    Thanks for your time RoyUK.

    I am passing my column values to xsl and it is therefore required to convert all cell values to text.

    I was doing this manually by using the formula say in cell("D2") =Text ("B2",0) and then copying cell("D2") and doing a paste special in cell "B2" so that once i get converted to text i can delete cell D2.

    Now i want to do this with VBA.

    Hope this is clear to help me. Please write if you want any more details to assist me.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    26th January 2003
    Location
    Indiana, USA
    Posts
    784

    Re: To convert cell content into text using VBA

    Possibly...
    VB:
    Sub Convert_To_Text() 
        Dim vData As Variant 
        vData = Columns(2) 
        Columns(2).NumberFormat = "@" 
        Columns(2) = vData 
    End Sub 
    
    

    Excel Video Tutorials / Excel Dashboards Reports


  5. #5
    Join Date
    19th October 2005
    Posts
    18

    Re: To convert cell content into text using VBA

    Thanks dangelor,

    but when i pass this column to xsl then i see the cell content which has the value 67788 as 67788.0 in my script.

    in order to see only 67788 in my xsl script i want to conver 67788 into text so that i see the same cell content in my xsl script

    Much appreciating your help

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    25th May 2004
    Location
    central Europe
    Posts
    773

    Re: To convert cell content into text using VBA

    VB:
    Sub xxx() 
         
        x = Range("b65536").End(xlUp).Row 
         
         
        For a = 2 To x 
            Cells(a, 2).Value = "" & Cells(a, 2).Value 
             
        Next 
    End Sub 
    
    
    This based on what you originally posted. Mind that you used a variable named rows which you should not do since rows is used by excel internally.
    Last edited by h1h; June 12th, 2006 at 22:04.
    In formulae, depending on your locale, you might have to replace ; with , or vice versa.

  7. #7
    Join Date
    19th October 2005
    Posts
    18

    Re: To convert cell content into text using VBA

    Many thanks h1h, its works as expected and thanks for pointing out the reserved word rows.

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Convert Cell Value To Words/Text
    By verma76 in forum EXCEL HELP
    Replies: 4
    Last Post: April 8th, 2008, 17:19
  2. Replies: 5
    Last Post: February 26th, 2008, 07:16
  3. Convert Text Times In 1 Cell
    By pilch in forum EXCEL HELP
    Replies: 8
    Last Post: June 8th, 2007, 13:35
  4. Search Using Cell Content/Text
    By belfast-biker in forum EXCEL HELP
    Replies: 3
    Last Post: October 5th, 2006, 06:26
  5. Convert binary file content to text
    By pbassett2223 in forum EXCEL HELP
    Replies: 5
    Last Post: May 9th, 2006, 07:54

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