Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.excel.programming > #109082

Re: Convert Numeric Values in Column to Text via VBA

Show all headers | View raw

Hi,

Am Tue, 12 Jul 2016 23:06:25 -0700 (PDT) schrieb seanryanie@yahoo.co.uk:

> 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

Back to microsoft.public.excel.programming | Previous | Next — Previous in thread | Next in thread | Find similar | Unroll thread

Thread

Convert Numeric Values in Column to Text via VBA seanryanie@yahoo.co.uk - 2016-07-12 23:06 -0700
  Re: Convert Numeric Values in Column to Text via VBA Claus Busch <claus_busch@t-online.de> - 2016-07-13 15:36 +0200
    Re: Convert Numeric Values in Column to Text via VBA seanryanie@yahoo.co.uk - 2016-07-13 06:54 -0700
      Re: Convert Numeric Values in Column to Text via VBA seanryanie@yahoo.co.uk - 2016-07-13 07:09 -0700

csiph-web