Multiplying with VBA

Question

I have a form where i need to multiply 3 different values to provide the desired result. It is below:

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

I need to either use the variables and have them calculate each or I would like to use the answers themselves to get the desired result.

How can I accomplish this?

Answer 1

I am assuming that the error is because you are trying to multiply strings of text that happen to be numeric characters. They will need converted to numbers. I would use Long type, or Double, depending on what kind of numbers you are using. Integer is more likely to have errors with varying user inputs in the text boxes. I'm going to use Long in the example, because it's closest to Integer, but if the numbers get very large, or use decimals, I'd switch them to Double.

I've modified a few other things about your code. Mainly in getting the .Text Property of the text boxes and converting them to Long variables. Also, in the declaration of addresses, instead of using Offset from A1, just declare the address using '.Cells(Row,Col)'. In general, your logic seems right.

Try this and see if there are any errors.

Code:

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