我有一个表格，需要将3个不同的值相乘才能提供所需的结果。如下：
Private Sub cmdAdd_Click()
Dim emptyRow As Long
Dim ctl As Control
Dim A As Integer
Dim B As Integer
Dim C As Integer
Dim D As Integer
Dim E As Integer
Dim Answer As Integer
Dim Answer2 As Integer
Dim Answer3 As Integer
If Me.txtNoTotalAff.Value = "" Then
MsgBox "Please enter the Total Number of Affiliates", vbExclamation, "ROI"
Me.txtNoTotalAff.SetFocus
Exit Sub
End If
If Me.lstClientName.Value = "" Then
MsgBox "Please enter Client Name", vbExclamation, "ROI"
Me.lstClientName.SetFocus
Exit Sub
End If
'Determine Empty Row
emptyRow = Worksheets("ROI").Range("A1").CurrentRegion.Rows.Count
A = txtNoTotalAff.Value
B = txtActiveAff.Value
C = txtAvgTraffic.Value
D = txtConvRate.Value
E = txtAOV.Value
Answer = A * B
Answer2 = Answer * C
Answer3 = (Answer2 * D) * E
'Transfer Information
With Worksheets("ROI").Range("A1")
.Offset(emptyRow, 0).Value = lstClientName.Value
.Offset(emptyRow, 1).Value = txtNoTotalAff.Value
.Offset(emptyRow, 2).Value = txtActiveAff.Value
.Offset(emptyRow, 3).Value = Answer
.Offset(emptyRow, 4).Value = txtAvgTraffic.Value
.Offset(emptyRow, 5).Value = Answer2
.Offset(emptyRow, 6).Value = txtConvRate.Value
.Offset(emptyRow, 7).Value = txtAOV.Value
.Offset(emptyRow, 8).Value = Answer3
.Offset(emptyRow, 9).Value = txtAffName.Value
End With
'Clear the Form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
我需要使用变量并让它们计算每个变量，或者我想使用答案本身来获得所需的结果。
我怎样才能做到这一点？
我假设该错误是因为您试图乘以恰好是数字字符的文本字符串。他们将需要转换为数字。我会使用Long类型或Double，这取决于您使用的是哪种数字。当文本框中的用户输入变化时，Integer更有可能出现错误。在示例中，我将使用Long，因为它最接近Integer，但是如果数字很大或使用小数，我会将其切换为Double。
我已经修改了有关您代码的其他一些内容。主要是获得文本框的.Text属性并将其转换为Long变量。同样，在地址声明中，不要使用'.Cells（Row，Col）'声明地址，而不是使用A1的Offset。通常，您的逻辑似乎正确。
试试这个，看看是否有任何错误。
代码：
Private Sub cmdAdd_Click()
Dim emptyRow As Long
Dim ctl As Control
Dim A As Long, B As Long, C As Long, D As Long, E As Long
Dim Answer As Long, Answer2 As Long, Answer3 As Long
'Validation
If txtNoTotalAff.Text= "" Then
MsgBox ("Please enter the Total Number of Affiliates", vbExclamation, "ROI")
txtNoTotalAff.SetFocus
Exit Sub
End If
If Me.lstClientName.Value = "" Then
MsgBox ("Please enter Client Name", vbExclamation, "ROI")
lstClientName.SetFocus
Exit Sub
End If
'Determine Empty Row
emptyRow = Sheets("ROI").Range("A" & Rows.count).End(xlUp).row + 1
A = CLng(txtNoTotalAff.Text)
B = CLng(txtActiveAff.Text)
C = CLng(txtAvgTraffic.Text)
D = CLng(txtConvRate.Text)
E = CLng(txtAOV.Text)
Answer = A * B
Answer2 = Answer * C
Answer3 = (Answer2 * D) * E
'Transfer Information
Sheets("ROI").Cells(emptyRow, 1) = lstClientName.Text 'Col "A"
Sheets("ROI").Cells(emptyRow, 2 ) = txtNoTotalAff.Text 'Col "B"
Sheets("ROI").Cells(emptyRow, 3) = txtActiveAff.Text 'Col "C"
Sheets("ROI").Cells(emptyRow, 4) = Answer 'Col "D"
Sheets("ROI").Cells(emptyRow, 5) = txtAvgTraffic.Text 'Col "E"
Sheets("ROI").Cells(emptyRow, 6) = Answer2 'Col "F"
Sheets("ROI").Cells(emptyRow, 7) = txtConvRate.Text 'Col "G"
Sheets("ROI").Cells(emptyRow, 8) = txtAOV.Text 'Col "H"
Sheets("ROI").Cells(emptyRow, 9) = Answer3 'Col "I"
Sheets("ROI").Cells(emptyRow, 10) = txtAffName.Value 'Col "J"
'Clear the Form
For Each ctl In Me.Controls
If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
ctl.Value = ""
ElseIf TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl
End Sub
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句