用户表单输入值的乘法和除法错误，以便在列a中获得新的值范围

Question

我在这行收到一个运行时错误13

"Cell.Value = (Cell.Value * UserForm2.InputBoxValue.Value) / 60"

我的用户表单叫做UserForm2，从用户那里获取输入数据的文本框叫做InputBoxValue。

Sub plottinggraph()
Dim LastRowOfA As Long, LastRowOfB As Long, LastRowOfG As Long
Dim ColumnARngData As Range

LastRowOfA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRowOfB = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set ColumnARngData = ActiveSheet.Range("A4:A" & LastRowOfA)

For Each Cell In ColumnARngData
Cell.Value = (Cell.Value * UserForm2.InputBoxValue.Value) / 60
Next Cell
End Sub

Answer 1

尽量避免在对象的原始数据中做数学运算。以声明的格式添加一些变量。注释

Sub plottinggraph()
Dim LastRowOfA As Long, LastRowOfB As Long, LastRowOfG As Long
Dim ColumnARngData As Range
dim tbData as long    ' variable for textbox value, if you have float/double format just change        

tbData = clng(UserForm2.InputBoxValue.Value) ' clng for sure
if not isnumeric(tbData ) then msgbox "NaN",vbcritical,"" : end ' condition to check is tbdata is number
LastRowOfA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRowOfB = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set ColumnARngData = ActiveSheet.Range("A4:A" & LastRowOfA)

For Each Cell In ColumnARngData
if not isnumeric(Cell.value) then msgbox "NaN",vbcritical,"" : end ' condition to check is cell value is number
Cell.Value = (Cell.Value * tbData ) / 60
Next Cell
End Sub

//编辑

Sub plottinggraph()
Dim LastRowOfA As Long, LastRowOfB As Long, LastRowOfG As Long
Dim ColumnARngData As Range
Dim tbData As Double ' variable for textbox value, if you have float/double format just change
Dim form As UserForm2 ' var for userform

Set form = UserForm2
If form.InputBoxValue.Value = "" Then ' condition for check is empty
MsgBox "NaN", vbCritical, "": End '
Else
On Error Resume Next
tbData = form.InputBoxValue.Value
If Err Then MsgBox "NaN", vbCritical, "": End '
On Error GoTo 0
End If
LastRowOfA = ActiveSheet.Cells(Rows.Count, 1).End(xlUp).Row
LastRowOfB = ActiveSheet.Cells(Rows.Count, 2).End(xlUp).Row
Set ColumnARngData = ActiveSheet.Range("A4:A" & LastRowOfA)

For Each Cell In ColumnARngData
If Not IsNumeric(Cell.Value) Then MsgBox "NaN", vbCritical, "": End ' condition to check is cell value is number
Cell.Value = (Cell.Value * tbData) / 60
Next Cell
End Sub