Announcement

Collapse
No announcement yet.

To convert cell content into text using VBA

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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
    Code:
    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.

  • #2
    Re: To convert cell content into text using VBA

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

    Roy

    New users should read the Forum Rules before posting

    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.

    Comment


    • #3
      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.

      Comment


      • #4
        Re: To convert cell content into text using VBA

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

        Comment


        • #5
          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

          Comment


          • #6
            Re: To convert cell content into text using VBA

            Code:
            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, 21:04.
            In formulae, depending on your locale, you might have to replace ; with , or vice versa.

            Comment


            • #7
              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.

              Comment

              Working...
              X