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
– Scott Holtzman, Commented Mar 14, 2017 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
– moe, Commented Mar 14, 2017 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
– Scott Craner, Commented Mar 14, 2017 at 21:51
To clarify, you want to run the code on the Detail worksheet when the Summary worksheet is activated...? — user4039065
– user4039065, Commented Mar 14, 2017 at 22:03

Community · Accepted Answer · 2017-05-23 12:17:20Z

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).

Collectives™ on Stack Overflow

how to convert text into number format using vba in excel

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related