Excel VBA-将以用户形式输入的字符串转换为数字 [英] Excel VBA - Convert string entered in user form to number

问题描述

我有一个excel用户表单，用户可以在其中输入数字，当这些数字输入到电子表格中时，他们会出现通知，指出这是一个以文本形式存储的数字.= SUM(H6:H13)显示零结果.我尝试过 NumCrtn = cLng(NumCrtn)-不会将单元格更改为数字，公式仍显示为零.我尝试过 NumCrtn = Val(NumCrtn)-不会将单元格更改为数字，公式仍显示为零.

I have an excel user form into which the user enters numbers, when those numbers are entered into the spreadsheet they appear with the notification that this is a number stored as text. =SUM(H6:H13) shows a zero result. I have tried NumCrtn = cLng(NumCrtn) - doesn't change the cell to a number, formula still shows zero. I have tried NumCrtn = Val(NumCrtn) - doesn't change the cell to a number, formula still shows zero.

我尝试将copy.special复制和粘贴为一个值，并且也不会将其更改为数字.不知道该怎么办.

I have tried copy and paste.special to a value and that doesn't change it to a number either. Don't know what to do.

帮助！

推荐答案

尝试以下方法:

With Range("H6:H13")
    .NumberFormat = "0"
    .Value = .Value
End With

另一种解决方案.基于Pradeep Kumar的建议，即在输入数据之前准备范围的建议，将代码更改为类似的内容

Another solution. Building on Pradeep Kumar's suggestion which deals with preparing your range before you enter the data, Change your code to something like this

Private Sub UserForm_Initialize()
    Dim aCell As Range

    Range("H6:H13").NumberFormat = "0"

    'This is to cater for any previous values if filled in
    For Each aCell In Range("H6:H13")
        aCell.Formula = aCell.Value
    Next
End Sub

Private Sub CommandButton1_Click()
    'Entering value for H6
    Range("H6").Value = TextBox1.Value
End Sub

这篇关于Excel VBA-将以用户形式输入的字符串转换为数字的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！