CODEWITHSUNDEEP
vbaexcel
Annie Jeba
Annie Jeba

Reputation: 379

How can we convert number to text using Macro?

I wanted to convert Column E to text format before applying logic to concatenate columns.

Presently Column E has both text and number values.

Existing code:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim lrw As Integer
Dim rgn As Range
Dim x As Integer
Dim str As String


lrw = ActiveSheet().Range("A1").End(xlDown).Row
Set rgn = Range("A1:E" & lrw)
rgn.Select
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("A2:A100000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers   '1th order by ID
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("C2:C100000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal        '2th order by date
ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Add Key:=Range("D2:D100000") _
    , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortTextAsNumbers   '3th order by Counter

With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange rgn
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin

    .Apply
End With
    For i = 2 To lrw
        x = Application.WorksheetFunction.CountIfs(Worksheets("Sheet1").Range("E2:E" & lrw), "*", Worksheets("Sheet1").Range("A2:A" & lrw), Cells(i, 1), Worksheets("Sheet1").Range("C2:C" & lrw), Cells(i, 3))
        If x > 1 Then

            cmnts = CStr(Cells(i, 5))
          For J = 1 To x - 1
          cmnts = cmnts & " " & CStr(Cells(i + J, 5))
            Rows(i + J).Select
            Selection.ClearContents
            Next J
            Cells(i, 5) = cmnts
        End If
     Next i
With ActiveWorkbook.Worksheets("Sheet1").Sort
    .SetRange rgn
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

I wanted to convert Column E to text format before applying logic to concatenate columns.

Upvotes: 1

Views: 2338

Answers (2)

0m3r
0m3r

Reputation: 12499

Try this

Sub Example()
    With ActiveWorkbook.Worksheets("Sheet1").Range("E:E")
        .NumberFormat = "@"
    End With
End Sub

Upvotes: 1

Karaelfte
Karaelfte

Reputation: 143

Try to use the CStr() function.

Upvotes: 0

Related Questions