Multiplying with VBA

Question

我有一个表格，我需要将 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

我需要使用变量

并让它们计算每个变量，或者我想使用答案本身来获得所需的结果。

我怎样才能做到这一点？

Answer 1

我假设错误是因为您尝试将恰好是数字字符的文本字符串相乘。 它们将需要转换为数字。 我会使用长型或双倍，具体取决于您使用的数字类型。 整数更有可能在文本框中出现不同的用户输入时出错。 我将在示例中使用 Long，因为它最接近整数，但如果数字变得非常大或使用小数，我会将它们切换为 Double。

我已经修改了有关您的代码的其他一些内容。 主要在获得.文本框的文本属性，并将其转换为 Long 变量。 此外，在地址声明中，不要使用 A1 的偏移量，只需使用 '.单元格（行，列）"。 总的来说，你的逻辑似乎是对的。

试试这个，看看是否有任何错误。

法典：

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