Cell Formating with VBA

[Brian_Richmond]

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

 

[Brian_Richmond]

Here is the fix I figured out on my own.... Helps if I actually use all of the variables I defined Heh! :wink: 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