VBA Convert Text into Number

[faxee97]

Hi Team,

Not good into Macro's. Can someone help me in fixing the below by converting text into numbers. My prefill formulas are not working and I have to manually convert my inserted number everytime from text to number.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'Update by Extendoffice: 2020/01/16
    Dim xCombox As OLEObject
    Dim xStr As String
    Dim xWs As Worksheet
    Dim xArr
    
    Set xWs = Application.ActiveSheet
    On Error Resume Next
    Set xCombox = xWs.OLEObjects("TempCombo")
    With xCombox
        .ListFillRange = ""
        .LinkedCell = ""
        .Visible = False
    End With
    If Target.Validation.Type = 3 Then
        Target.Validation.InCellDropdown = False
        Cancel = True
        xStr = Target.Validation.Formula1
        xStr = Right(xStr, Len(xStr) - 1)
        If xStr = "" Then Exit Sub
        With xCombox
            .Visible = True
            .Left = Target.Left
            .Top = Target.Top
            .Width = Target.Width + 5
            .Height = Target.Height + 5
            .ListFillRange = xStr
            If .ListFillRange = "" Then
                xArr = Split(xStr, ",")
                Me.TempCombo.List = xArr
            End If
            .LinkedCell = Target.Address
        End With
        xCombox.Activate
        Me.TempCombo.DropDown
    End If
End Sub
Private Sub TempCombo_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    Select Case KeyCode
        Case 9
            Application.ActiveCell.Offset(0, 1).Activate
        Case 13
            Application.ActiveCell.Offset(1, 0).Activate
    End Select
End Sub

 

[daverunt]

Not sure what your code is doing with the formula part. If it's just 'numbers' try:

CInt or CLng

e.g Me.TempCombo.List = CInt (xArr)

VBA Convert Text String to Number - Automate Excel

In this ArticleConvert String to IntegerConvert String to DecimalConvert String to Currency You may be required to convert numbers stored as text to actual numbers in your VBA code. In this tutorial, we are going to go over the functions that you need to use to convert a string to an integer...

www.automateexcel.com

 

[faxee97]

Not sure what your code is doing with the formula part. If it's just 'numbers' try:

CInt or CLng

e.g Me.TempCombo.List = CInt (xArr)

VBA Convert Text String to Number - Automate Excel

In this ArticleConvert String to IntegerConvert String to DecimalConvert String to Currency You may be required to convert numbers stored as text to actual numbers in your VBA code. In this tutorial, we are going to go over the functions that you need to use to convert a string to an integer...

www.automateexcel.com

Hi There,

It's not working. Are you able to fix in the above so that I can simply copy it. I got this from the internet to prefill my data Validation.

Thanks

 

[mohadin]

Hi,
Try
*1

 

[faxee97]

Hi,
Try
*1

Hi Mohadin,

Where about should I add *1 ?

Thanks

 

[mohadin]

In general If you have a string variable holding number (say: x=987)
Then x*1 to convert the string to number
So..

 

[daverunt]

Can you post an example of the formula and what it produces in your list.