Code Checking Please

[LoneReaper]

Hi everyone.

Got another one for the VBA gurus if they don't mind. So I think I'm making progress on understanding Dim and producing the results I want, however there is still some ... complications I am having with my current code.

What I would like to achieve is the following:
Client A wishes to purchase an item and the item comes to X dollar value.
This client also receives a commission off this item (Y). Depending on how much this client has spend their commission value may increase during the order.

For example:
Say a client orders an item worth $4,000. This client has also already ordered $18,000 worth of goods and at $20,000 their commission value will increase. Therefore this particular order would be charged 35% commission on $2,000 and 40% commission on the remaining $2,000.

The code I am using is as follows:


If CheckBox10.Value = True Then
Dim mcmessage, mctitle, mcdefault, fmcresult, fmc, fmcComm1, fmcComm2
mcmessage = "Enter Brokers Current Total $ of Sales"
mctitle = "Multiple Commissions"
mcdefault = "0"
fmc = InputBox(mcmessage, mctitle, mcdefault)

If fmc + TextBox7 < 20000 Then
TextBox9 = TextBox7.Value - (((Val(35 / 100)) * TextBox7.Value))
TextBox10.Value = Format(Val((TextBox9.Value) * Val(0.1)), "###0.00")
TextBox11.Value = Format(Val(TextBox9.Value) + Val(TextBox10.Value), "###0.00")

ElseIf fmc + TextBox7.Value < 40000 Then
fmcComm1 = fmc + TextBox7 - Val(20000)
fmcComm2 = TextBox7 - fmcComm1
TextBox9 = (fmcComm2 - ((Val(35 / 100)) * fmcComm2)) + (fmcComm1 - ((Val(40 / 100)) * fmcComm1.Value))
Set fmcresult = ActiveDocument.Tables(1).Cell(18, 2).Range
fmcresult.Text = "(35 - 40%)"
TextBox10.Value = Format(Val((TextBox9.Value) * Val(0.1)), "###0.00")
TextBox11.Value = Format(Val(TextBox9.Value) + Val(TextBox10.Value), "###0.00")

ElseIf fmc + TextBox7.Value < 60000 Then
fmcComm1.Value = fmc + TextBox7.Value - Val(40000)
fmcComm2.Value = TextBox7.Value - fmcComm1.Value
TextBox9.Value = (fmcComm2.Value - ((Val(40 / 100)) * fmcComm2.Value)) + (fmcComm1.Value - ((Val(45 / 100)) * fmcComm1.Value))
Set fmc = ActiveDocument.Tables(1).Cell(18, 2).Range
fmc.Text = "(40 - 45%)"
TextBox10.Value = Format(Val((TextBox9.Value) * Val(0.1)), "###0.00")
TextBox11.Value = Format(Val(TextBox9.Value) + Val(TextBox10.Value), "###0.00")

ElseIf fmc + TextBox7.Value > 60000 Then
fmcComm1 = fmc + TextBox7.Value - Val(60000)
fmcComm2 = TextBox7.Value - fmcComm1
TextBox9.Value = (fmcComm2 - ((Val(45 / 100)) * fmcComm2)) + (fmcComm1 - ((Val(50 / 100)) * fmcComm1))
Set fmc = ActiveDocument.Tables(1).Cell(18, 2).Range
fmc.Text = "(45 - 50%)"
TextBox10.Value = Format(Val((TextBox9.Value) * Val(0.1)), "###0.00")
TextBox11.Value = Format(Val(TextBox9.Value) + Val(TextBox10.Value), "###0.00")

End If
End If



If anyone would like me to explain any variables any further please let me know. I know I'll be wracking my brain over this and I think it would be easier if I saw where I am wrong so I can learn from the error.

Thank you in advance.

[gmayor]

Most of the problem is the illiberal use of Val() and an inadequate differentiation between strings and numbers. The mathematics I'll leave to you.
Also ensure that you declare the variables fully.
I am not sure why you are writing to the table at this point?

Try the following


Dim mcmessage As String, mctitle As String
Dim mcdefault As Long
Dim fmcresult As Range
Dim fmc As String
Dim lngFmc As Long
Dim fmcComm1 As Long, fmcComm2 As Long
Dim lng9 As Long, lng10 As Long

Start:
If CheckBox10.Value = True Then
mcmessage = "Enter Brokers Current Total $ of Sales"
mctitle = "Multiple Commissions"
mcdefault = "0"
fmc = InputBox(mcmessage, mctitle, mcdefault)
If Not IsNumeric(fmc) Then
MsgBox "Enter a number!"
GoTo Start:
End If

lngFmc = TextBox7.Value + val(fmc)

Set fmcresult = ActiveDocument.Tables(1).Cell(18, 2).Range
fmcresult.End = fmcresult.End - 1

If lngFmc < 20000 Then
TextBox9.Value = TextBox7.Value - ((35 / 100) * TextBox7.Value)
TextBox10.Value = Format(TextBox9.Value * 0.1, "###0.00")
lng9 = val(TextBox9.Text)
lng10 = val(TextBox10.Text)
TextBox11.Value = Format(lng9 + lng10, "###0.00")

ElseIf lngFmc < 40000 Then
fmcComm1 = lngFmc - 20000
fmcComm2 = TextBox7.Value - fmcComm1
TextBox9.Value = (fmcComm2 - (35 / 100) * fmcComm2) + (fmcComm1 - (40 / 100) * fmcComm1)
lng9 = val(TextBox9.Text)
fmcresult.Text = "(35 - 40%)"
TextBox10.Value = Format(lng9 * 0.1, "###0.00")
lng10 = val(TextBox10.Text)
TextBox11.Value = Format(lng9 + lng10, "###0.00")

ElseIf lngFmc < 60000 Then
fmcComm1 = lngFmc - 40000
fmcComm2 = TextBox7.Value - fmcComm1
TextBox9.Value = (fmcComm2 - (40 / 100) * fmcComm2) + (fmcComm1 - (45 / 100) * fmcComm1)
lng9 = val(TextBox9.Text)
fmcresult.Text = "(40 - 45%)"
TextBox10.Value = Format(lng9 * 0.1, "###0.00")
lng10 = val(TextBox10.Text)
TextBox11.Value = Format(lng9 + lng10, "###0.00")

Else
fmcComm1 = lngFmc - 60000
fmcComm2 = TextBox7.Value - fmcComm1
TextBox9.Value = (fmcComm2 - (45 / 100) * fmcComm2) + (fmcComm1 - (50 / 100) * fmcComm1)
lng9 = val(TextBox9.Text)
fmcresult.Text = "(45 - 50%)"
TextBox10.Value = Format(lng9 * 0.1, "###0.00")
lng10 = val(TextBox10.Text)
TextBox11.Value = Format(lng9 + lng10, "###0.00")
End If
End If

[gmaxey]

Just a second opinion. Variables are important and necessary, but my goal is not to use anymore than I need to make the code functional and readable. While nothing wrong with them, I usually avoid GoTo when I can and I prefer Select Case over multiple If ... Else If.


Dim strFMC As String
Dim oRngFMCResult As Range
Dim lngFmc As Long
Dim lngFMCComm1 As Long, lngFMCComm2 As Long
If CheckBox10 Then
Do
strFMC = InputBox("Enter Brokers Current Total $ of Sales", "Multiple Commissions", "0")
Loop Until IsNumeric(strFMC)
lngFmc = Val(TextBox7) + Val(strFMC)
Set oRngFMCResult = ActiveDocument.Tables(1).Cell(18, 2).Range
oRngFMCResult.End = oRngFMCResult.End - 1
Select Case True
Case lngFmc < 20000
TextBox9.Value = TextBox7.Value - ((35 / 100) * TextBox7.Value)
TextBox10.Value = Format(TextBox9.Value * 0.1, "###0.00")
Case lngFmc < 40000
lngFMCComm1 = lngFmc - 20000
lngFMCComm2 = TextBox7.Value - lngFMCComm1
TextBox9.Value = (lngFMCComm2 - (35 / 100) * lngFMCComm2) + (lngFMCComm1 - (40 / 100) * lngFMCComm1)
oRngFMCResult.Text = "(35 - 40%)"
TextBox10.Value = Format(Val(TextBox9) * 0.1, "###0.00")
Case lngFmc < 60000
lngFMCComm1 = lngFmc - 40000
lngFMCComm2 = TextBox7.Value - lngFMCComm1
TextBox9.Value = (lngFMCComm2 - (40 / 100) * lngFMCComm2) + (lngFMCComm1 - (45 / 100) * lngFMCComm1)
oRngFMCResult.Text = "(40 - 45%)"
TextBox10.Value = Format(Val(TextBox9) * 0.1, "###0.00")
Case Else
lngFMCComm1 = lngFmc - 60000
lngFMCComm2 = TextBox7.Value - lngFMCComm1
TextBox9.Value = (lngFMCComm2 - (45 / 100) * lngFMCComm2) + (lngFMCComm1 - (50 / 100) * lngFMCComm1)
oRngFMCResult.Text = "(45 - 50%)"
TextBox10.Value = Format(Val(TextBox9) * 0.1, "###0.00")
End Select
TextBox11.Value = Format(Val(TextBox9) + Val(TextBox10), "###0.00")
End If
End Sub

[LoneReaper]

Both codes work so thank you both for them.
gmayor - You are correct I didn't need to write to the table at this stage. The fmcresult.Text was going to be written once it was known to the table, which should come at the very last point.