Brian_Richmond
New Member
- Joined
- Apr 10, 2002
- Messages
- 14
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
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