Cell Formating with VBA
Upcoming Power Excel Seminars
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Cell Formating with VBA

  1. #1
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    I receive many reports in with cells in various formats. I have written a small script that I am trying to get a given cell within a manually selected range to have it's value temporarily stored in a variable, clear the contents of the cell, format the cell as text, and write the temporary stored value into the newly formatted cell as text.

    I need this for MATCH & VLOOKUP across multiple worksheets which don't work as I hoped when the Cells are formatted differently.

    I have tried to use the .NumberFormat = "@" but it won't change Currency, Date, and others to Text. I am including a sample of the code:

    Option Explicit
    Sub Range_Conversion()
    Dim Selection As Object
    Dim Target As Variant
    Dim Result As String
    Dim Y_Axis As Integer

    Y_Axis = 0

    For Each Selection In ActiveCell.CurrentRegion.Cells
    Target = Selection.Value
    ActiveCell.Offset(Y_Axis, 2).ClearContents
    ActiveCell.Offset(Y_Axis, 2).NumberFormat = "@"
    ActiveCell.Offset(Y_Axis, 2) = Target
    Y_Axis = Y_Axis + 1
    Next

    End Sub

  2. #2
    New Member
    Join Date
    Apr 2002
    Posts
    14
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Here is the fix I figured out on my own.... Helps if I actually use all of the variables I defined Heh! The Column offset of 2 was just for visual testing purposes, the final script does use column offset of 0.


    Option Explicit
    Sub Range_Conversion()
    Dim Selection As Object
    Dim Target As Variant
    Dim Result As String
    Dim Y_Axis As Integer

    Y_Axis = 0

    For Each Selection In ActiveCell.CurrentRegion.Cells
    Target = Selection.Value
    Result = Target
    ActiveCell.Offset(Y_Axis, 0).ClearContents
    ActiveCell.Offset(Y_Axis, 0).NumberFormat
    = "@"
    ActiveCell.Offset(Y_Axis, 0) = Result
    Y_Axis = Y_Axis + 1
    Next

    End Sub

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com