CODEWITHSUNDEEP
vbaexcel
brittd
brittd

Reputation: 15

VBA convert numbers stored as text to numbers on cell change

Using the below code to convert numbers stored as text to numbers. How would i automate this macro to trigger on cell changes?

eg. as soon as i paste data onto the spreadsheet from another source, I want the newly pasted cells within range Y:Y to convert numbers stored as text to numbers

Have tried a few different things, but the cells are only converted when set the macro to run manually.

Sub macro()

Range("Y:Y").Select 
With Selection
    Selection.NumberFormat = "0"
    .Value = .Value
End With

End Sub

Upvotes: 1

Views: 2064

Answers (1)

Shai Rado
Shai Rado

Reputation: 33662

Add the code below to your Worksheet's module, to the Worksheet_Change event.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("Y:Y")) Is Nothing Then
    Application.EnableEvents = False

    With Target
        .NumberFormat = "0"

        If InStr(.Value, "`") = 1 Then ' if leading character is apostrophe
            .Value = Mid(.Value, 2) ' start from second character
        Else ' regular number
            .Value = .Value
        End If
    End With
End If
Application.EnableEvents = True

End Sub

Upvotes: 4

Related Questions