how to convert text into number format using vba in excel

Question

I am having issues with converting text values into numeric values in Excel. I have excel sheet that has some text values and i would like to convert these text values into numbers using VBA or macro. The range that i always want to change starts from s2 to v2 columns and the number of rows are always dynamic. One day i can have 10 rows but next day i can have 20000 rows. However, i only want to run the macro when the summary sheet is activated in other words when the summary sheet is clicked i want to run the macro. please help. thanks

Sub TextToNumber()
'
' TextToNumber Macro
'

'    

        Dim sht As Worksheet
Dim LastRow As Long
Dim LastColumn As Long
Dim StartCell As Range

Set sht = Worksheets("Detail")
Range("AJ1").Select
Selection.Copy

Set StartCell = Range("S2:Y9")

'Find Last Row and Column
  LastRow = sht.Cells(sht.Rows.Count, StartCell.Column).End(xlUp).Row
  LastColumn = sht.Cells(StartCell.Row, sht.Columns.Count).End(xlToLeft).Column

'Select Range
  sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select

  Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlMultiply, _
        SkipBlanks:=False, Transpose:=False

        Sheets("Summary").Select
End Sub

Have you *actually tried anything*? You can use `TEXT` formula or `Range().NumberFormat` for vba. — Scott Holtzman, Mar 14 '17 at 21:26
i just updated and added what i have done so far but it is failing at this line Select Range sht.Range(StartCell, sht.Cells(LastRow, LastColumn)).Select — moe, Mar 14 '17 at 21:45
When you set a range you need to specify the sheet. Your startCell is probably referring to the wrong sheet and you are getting an error. `Set StartCell = sht.Range("S2:Y9")` — Scott Craner, Mar 14 '17 at 21:51
To clarify, you want to run the code on the **Detail** worksheet when the **Summary** worksheet is activated...? — , Mar 14 '17 at 22:03

score 3 · Accepted Answer · edited May 23 '17 at 12:17

Cycle through the columns applying Text-To-Columns, Fixed Width on each. If you want this to occur 'when the summary sheet is activated' then use that worksheet's code sheet's Worksheet_Activate event sub.

In the Summary worksheet's code sheet (i.e. right-click name tab then View Code)

Option Explicit

Private Sub Worksheet_Activate()
    Dim c As Long
    On Error Resume Next   '<~~ necessary in case there are no values to parse
    For c = 19 To 25       '<~~ from column S to column Y
        With Worksheets("Detail")
            With .Range(.Cells(2, c), .Cells(.Rows.Count, c).End(xlUp))
                .TextToColumns Destination:=.Cells(1, 1), DataType:=xlFixedWidth, FieldInfo:=Array(0, 1)
            End With
        End With
    Next c
End Sub

The TextToColumns is fast; so fast that you may not notice it operating at all. However, if you only have to do this once a day then you may want to record the date of the last processing so you not run it several times per day (every time you activate the Summary worksheet).

It is very important to fully qualify any cell range references on the Detail worksheet. You are a private sub on the Summary worksheet's code sheet and any unqualified range references will default to the Summary worksheet, not the Detail worksheet (see Is the . in .Range necessary when defined by .Cells? for more information).

how to convert text into number format using vba in excel

1 Answers1