我想根据交货地点(当地、地区、国家(和运输重量计算运输成本。运费基于三种不同类别的包装类型(标准、超大或散装(，也基于重量。
我创建了嵌套的案例，但它只适用于本地的第一个案例，而不适用于其他案例(地区和国家(。请帮助我更正此代码并解释的错误
Dim WT 'WT is Package Weight Type (Standard, Oversize or Bulk)
Dim STD ' Standard
Dim OVS 'Oversize
Dim BLK 'Bulk Size
Dim LC ' Location
Dim LCL 'Local
Dim RGN ' Regional
Dim NTN ' National
Dim EF ' Effective Weight on which cost will be calculated
Dim SHL ' Shipping cost
Private Sub Shipping_calc()
Select Case WT
Case Is <= STD
Select Case LC
Case Is <= LCL
Select Case EF
Case Is <= 0.5: SHL = "38"
Case Is <= 1: SHL = "54"
Case Is <= 2: SHL = "64"
Case Is <= 3: SHL = "74"
Case Is <= 4: SHL = "84"
Case Is < 5: SHL = "94"
End Select
Case Is <= RGN
Select Case EF
Case Is <= 0.5: SHL = "46"
Case Is <= 1: SHL = "67"
Case Is <= 2: SHL = "82"
Case Is <= 3: SHL = "97"
Case Is <= 4: SHL = "112"
Case Is < 5: SHL = "127"
End Select
Case Is <= NTN
Select Case EF
Case Is <= 0.5: SHL = "66"
Case Is <= 1: SHL = "91"
Case Is <= 2: SHL = "111"
Case Is <= 3: SHL = "131"
Case Is <= 4: SHL = "151"
Case Is < 5: SHL = "171"
End Select
End Select
Case Is <= OVS
Select Case LC
Case Is <= LCL
Select Case EF
Case Is <= 5: SHL = "101"
Case Is >= 5: SHL = (EF - 5) * 10 + 101
End Select
Case Is <= RGN
Select Case EF
Case Is <= 5: SHL = "116"
Case Is >= 5: SHL = (EF - 5) * 10 + 116
End Select
Case Is <= NTN
Select Case EF
Case Is <= 5: SHL = "166"
Case Is >= 5: SHL = (EF - 5) * 10 + 166
End Select
End Select
Case Is <= BLK
Select Case LC
Case Is <= LCL
Select Case EF
Case Is <= 12: SHL = "241"
Case Is >= 12: SHL = (EF - 12) * 3 + 241
End Select
Case Is <= RGN
Select Case EF
Case Is <= 12: SHL = "241"
Case Is >= 12: SHL = (EF - 12) * 4 + 321
End Select
Case Is <= NTN
Select Case EF
Case Is < 0 > 100: SHL = "NA"
End Select
End Select
End Select
End Sub
代码是cleaer，但不知何故它不起作用，所以我试图更改它现在从这里粘贴完整的用户代码
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub cmdDelete_Click()
Dim X As Long
Dim Y As Long
X = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For Y = 6 To X
If Sheet1.Cells(Y, 1).Value = TextBox12.Text Then
Rows(Y).Delete Shift:=xlUp
End If
Next Y
End Sub
Private Sub cmdProfit_Click()
Dim X As Long
Dim Y As Long
Dim RF 'RF Refferal Fee%
Dim FF 'FF Fixed fee
Dim SF 'Ship Fee
Dim TS ' Total Sale Amount
Dim LC
Dim EF
Dim VM
Dim AW
Dim WT
Dim PR ' Profit
'WT is Weight Type Standard Oversize or Bulk
'Loc is location Local regional or National
'AW is Actual Weight
'VM is Volumatric Weight
'EF is Effective Weight Volumatric aor actual whichever is greater
X = Sheet1.Range("BA" & Rows.Count).End(xlUp).Row
For Y = 1 To X
If Sheet1.Cells(Y, 53).Value = ComboBox1.Text Then
RF = Sheet1.Cells(Y, 54).Value
End If
Next Y
TextBox17 = RF * 100 'multiples 100 Only to show %value good, no referecen text box for this calc
TS = Val(TextBox5.Text) + (Val(TextBox5.Text) * Val(ComboBox3.Text))
TextBox13 = TS
If TS > 0 And TS <= 250 Then
FF = 2
End If
If TS > 250 And TS <= 500 Then
FF = 5
End If
If TS > 500 And TS <= 1000 Then
FF = 25
End If
If TS > 1000 Then
FF = 50
End If
'Dim LCL
'Dim RGN
'Dim NTN
If Opt2.Value = True Then
LC = "Local"
End If
If Opt3.Value = True Then
LC = "Regional"
End If
If Opt4.Value = True Then
LC = "National"
End If
'Weight calculation starts from here
VM = Val(TextBox6.Text) * Val(TextBox7.Text) * Val(TextBox8.Text) / 5000 'Weight in KG
AW = TextBox9.Value / 1000 'Weight in KG
If VM > AW Then
EF = VM
End If
If AW > VM Then
EF = AW
End If
'Dim STD 'Standard
'Dim OVS 'Oversize
'Dim BLK 'Bulk
If EF < 5 Then
WT = "Standard"
End If
If EF >= 5 And EF <= 12 Then
WT = "Oversize"
End If
If EF > 12 Then
WT = "Bulk"
End If
'Shipping Charges calculation
Dim SH
Dim SHL
Dim SHR
Dim SHN
'SHL for shipping cost shl used in case so SH is nt required
'SHR for Regional
'SHN for National
Select Case WT
Case Is <= "Standard"
Select Case EF
Case Is <= 0.5 And LC = "Local": SHL = 38
Case Is <= 1 And LC = "Local": SHL = 54
Case Is <= 2 And LC = "Local": SHL = 64
Case Is <= 3 And LC = "Local": SHL = 74
Case Is <= 4 And LC = "Local": SHL = 84
Case Is < 5 And LC = "Local": SHL = 94
Case Is <= 0.5 And LC = "Regional": SHL = 46
Case Is <= 1 And LC = "Regional": SHL = 67
Case Is <= 2 And LC = "Regional": SHL = 82
Case Is <= 3 And LC = "Regional": SHL = 97
Case Is <= 4 And LC = "Regional": SHL = 112
Case Is < 5 And LC = "Regional": SHL = 127
Case Is <= 0.5 And LC = "National": SHL = 66
Case Is <= 1 And LC = "National": SHL = 91
Case Is <= 2 And LC = "National": SHL = 111
Case Is <= 3 And LC = "National": SHL = 131
Case Is <= 4 And LC = "National": SHL = 151
Case Is < 5 And LC = "National": SHL = 171
End Select
Case Is <= "Oversize"
Select Case EF
Case Is <= 5 And LC = "Local": SHL = 101
Case Is >= 5 And LC = "Local": SHL = (EF - 5) * 10 + 101
Case Is <= 5 And LC = "Regional": SHL = 116
Case Is >= 5 And LC = "Regional": SHL = (EF - 5) * 10 + 116
Case Is <= 5 And LC = "National": SHL = 166
Case Is >= 5 And LC = "National": SHL = (EF - 5) * 10 + 166
End Select
Case Is <= "Bulk"
Select Case EF
Case Is <= 12 And LC = "Local": SHL = 241
Case Is >= 12 And LC = "Local": SHL = (EF - 12) * 3 + 241
Case Is <= 12 And LC = "Regional": SHL = 241
Case Is >= 12 And LC = "Regional": SHL = (EF - 12) * 4 + 321
Case Is <= 0 And LC = "National": SHL = "NA"
End Select
End Select
'Profit calc RFA Refferal fee amount
RFA = TS * RF
Dim SA 'Sale Amount with out tax
Dim CP 'Cost price without tax
Dim AC 'Total Amazon charge=Referreal fee amount + Fixed Fee +Shipping
Dim TC ' cost price + gst
SA = TextBox5.Value
CP = TextBox4.Value
OTEXP = Val(TextBox10.Text) + Val(TextBox11.Text)
INGST = TextBox4.Value * ComboBox2.Value
OUTGST = TextBox5.Value * ComboBox3.Value
TC = CP + INGST
AC = RFA + FF + SHL
TextBox14 = AC
PR = TS - AC - TC - OTEXP + INGST - OUTGST
TextBox15 = PR
TextBox16 = PR / CP * 100
If PR <= 0 Then
MsgBox ("Please make sure you have filled all the required box Correctly. Increase your sales price")
End If
MsgBox (SHL)
MsgBox (EF)
End Sub
Private Sub cmdProfit_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
For i = 4 To 9
If Userform1.Controls("TextBox" & i).Value = "" Then
Userform1.cmdProfit.Enabled = False
End If
Next i
For j = 1 To 3
If Userform1.Controls("ComboBox" & j).Value = "" Then
Userform1.cmdProfit.Enabled = False
End If
Next j
End Sub
Private Sub ComboBox1_Change()
End Sub
Private Sub cmdAdd_Click()
Dim X As Long
Dim Y As Worksheet
Set Y = Sheet1
X = Y.Range("A" & Rows.Count).End(xlUp).Row + 1
With Y
.Cells(X, 1).Value = TextBox1.Text
.Cells(X, 2).Value = ComboBox2.Text
.Cells(X, 3).Value = ComboBox3.Text
.Cells(X, 4).Value = TextBox4.Text
.Cells(X, 5).Value = TextBox5.Text
.Cells(X, 6).Value = ComboBox1.Text
If Opt2.Value = True Then
.Cells(X, 7).Value = "Local"
End If
If Opt3.Value = True Then
.Cells(X, 7).Value = "Regional"
End If
If Opt4.Value = True Then
.Cells(X, 7).Value = "National"
End If
.Cells(X, 8).Value = TextBox6.Text
.Cells(X, 9).Value = TextBox7.Text
.Cells(X, 10).Value = TextBox8.Text
.Cells(X, 11).Value = TextBox9.Text
.Cells(X, 12).Value = TextBox10.Text
.Cells(X, 13).Value = TextBox11.Text
Unload Me
Userform1.Show
End With
End Sub
Private Sub cmdReset_Click()
Unload Me
Userform1.Show
End Sub
Private Sub cmdSearch_Click()
Dim X As Long
Dim Y As Long
X = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For Y = 6 To X
If Sheet1.Cells(Y, 1).Value = TextBox12.Text Then
TextBox1 = Sheet1.Cells(Y, 1).Value
ComboBox2 = Sheet1.Cells(Y, 2).Value
ComboBox3 = Sheet1.Cells(Y, 3).Value
TextBox4 = Sheet1.Cells(Y, 4).Value
TextBox5 = Sheet1.Cells(Y, 5).Value
ComboBox1 = Sheet1.Cells(Y, 6).Value
If Sheet1.Cells(Y, 7).Value = "Local" Then
Opt2.Value = True
End If
If Sheet1.Cells(Y, 7).Value = "Regional" Then
Opt3.Value = True
End If
If Sheet1.Cells(Y, 7).Value = "National" Then
Opt4.Value = True
End If
TextBox6 = Sheet1.Cells(Y, 8).Value
TextBox7 = Sheet1.Cells(Y, 9).Value
TextBox8 = Sheet1.Cells(Y, 10).Value
TextBox9 = Sheet1.Cells(Y, 11).Value
TextBox10 = Sheet1.Cells(Y, 12).Value
TextBox11 = Sheet1.Cells(Y, 13).Value
End If
Next Y
End Sub
Private Sub cmdUpdate_Click()
Dim X As Long
Dim Y As Long
X = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
For Y = 6 To X
If Sheet1.Cells(Y, 1).Value = TextBox12.Text Then
Sheet1.Cells(Y, 1).Value = TextBox1.Value
Sheet1.Cells(Y, 2).Value = ComboBox2.Value
Sheet1.Cells(Y, 3).Value = ComboBox3.Value
Sheet1.Cells(Y, 4).Value = TextBox4.Value
Sheet1.Cells(Y, 5).Value = TextBox5.Value
Sheet1.Cells(Y, 6).Value = ComboBox1.Value
If Opt2.Value = True Then
Sheet1.Cells(Y, 7).Value = "Local"
End If
If Opt3.Value = True Then
Sheet1.Cells(Y, 7).Value = "Regional"
End If
If Opt4.Value = True Then
Sheet1.Cells(Y, 7).Value = "National"
End If
Sheet1.Cells(Y, 8).Value = TextBox6.Value
Sheet1.Cells(Y, 9).Value = TextBox7.Value
Sheet1.Cells(Y, 10).Value = TextBox8.Value
Sheet1.Cells(Y, 11).Value = TextBox9.Value
Sheet1.Cells(Y, 12).Value = TextBox10.Value
Sheet1.Cells(Y, 13).Value = TextBox11.Value
End If
Next Y
End Sub
Private Sub CommandButton6_Click()
End Sub
Private Sub ComboBox2_Change()
End Sub
Private Sub CommandButton1_Click()
End Sub
Private Sub CommandButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
MsgBox ("Please fill all the required box")
Userform1.cmdProfit.Enabled = True
End Sub
Private Sub Label1_Click()
End Sub
Private Sub Label15_Click()
End Sub
Private Sub Label16_Click()
End Sub
Private Sub TextBox1_Change()
End Sub
Private Sub TextBox13_Change()
End Sub
Private Sub TextBox17_Change()
End Sub
Private Sub UserForm_Click()
End Sub
Private Sub UserForm_Initialize()
TextBox1 = Application.WorksheetFunction.Max(Sheet1.Range("A:A")) + 1
End Sub
由于所有变量都已声明但未定义，因此它们都被视为相等，因此每个Case语句都为true。
VBA最多只执行一个Case语句——它发现的第一个语句为true。
如果testexpression匹配任何Caseexpressionlist表达式，则Case子句后面的语句将执行到下一个Case语句，或者，对于最后一个子句，执行到End Select。控制然后传递到结束选择下面的语句。如果testexpression在多个Case子句中与expressionlist表达式匹配，则只执行第一个匹配之后的语句。
参考：
- https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/select-case-statement