Does anyone have some VBA code that will convert all numeric values to text value in a certain column, in a certain worksheet?

Eg convert 630 to '630

The number of rows in my sheet can vary (I want to use this code on different workbooks also), so Code should loop through until last row of Data

Obviously existing Text values in the column should be ignored

Thanks

Hi,

Am Tue, 12 Jul 2016 23:06:25 -0700 (PDT) schrieb :

Does anyone have some VBA code that will convert all numeric values to text value in a certain column, in a certain worksheet?

Eg convert 630 to '630

The number of rows in my sheet can vary (I want to use this code on different workbooks also), so Code should loop through until last row of Data

Obviously existing Text values in the column should be ignored

why not simply set the numberformat for the column to "@"?

Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:A" & LRow).NumberFormat = "@"
End With
End Sub

If you want a prefix then try:

Sub Test2()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
If TypeName(rngC.Value) < "String" Then
rngC = "'" & rngC
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016

On Wednesday, July 13, 2016 at 2:36:58 PM UTC+1, Claus Busch wrote:
Hi,

Am Tue, 12 Jul 2016 23:06:25 -0700 (PDT) schrieb :

Does anyone have some VBA code that will convert all numeric values to text value in a certain column, in a certain worksheet?

Eg convert 630 to '630

The number of rows in my sheet can vary (I want to use this code on different workbooks also), so Code should loop through until last row of Data

Obviously existing Text values in the column should be ignored

why not simply set the numberformat for the column to "@"?

Sub Test()
Dim LRow As Long

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
.Range("A1:A" & LRow).NumberFormat = "@"
End With
End Sub

If you want a prefix then try:

Sub Test2()
Dim LRow As Long
Dim rngC As Range

With ActiveSheet
LRow = .Cells(.Rows.Count, 1).End(xlUp).Row
For Each rngC In .Range("A1:A" & LRow)
If TypeName(rngC.Value) < "String" Then
rngC = "'" & rngC
End If
Next
End With
End Sub


Regards
Claus B.
--
Windows10
Office 2016
Claus, many thanks, worked perfect the Test2 version. Test1 didn't really appear to do anything

On VBA code Test2, it seems to record within the code itself (after you run it) what it has done, is it supposed to do that?