excel - 选择嵌套在 Excel VBA 中的案例
问题描述
我想根据送货地点(本地、区域、国家)和运输重量计算运费。运费基于三种不同类别的包裹类型(标准、超大或散装)以及重量。
我创建了嵌套案例,但它仅适用于本地的第一个案例,不适用于其他案例(区域和国家)。请帮助我更正此代码并解释问题所在
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
代码更清晰,但不知何故它不起作用所以我尝试更改它现在从代码中粘贴完整的用户here
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
语句都是正确的。
VBA 最多只执行一个Case
语句——它发现的第一个语句是正确的。
如果testexpression匹配任何Case expressionlist表达式,则该Case子句之后的语句将执行到下一个Case子句,或者,对于最后一个子句,执行到End Select。然后控制权传递给End Select之后的语句。如果testexpression在多个Case子句中匹配expressionlist表达式,则仅执行第一个匹配项之后的语句。
参考:
推荐阅读
- bash - 使用 bash 循环浏览目录中的文件,然后根据文件名创建子目录
- javascript - AJAX 请求未与某些数据库一起发送
- java - 封闭表达式,以 n 表示
- api - IE 11 响应负载大小
- python - @cupy.fuse cupy python 装饰器记录在哪里?
- java - 当 Junit5 ParameterizedTest Attempt 时,Java Exception No Tests Found Matching
- javascript - 为什么在这个例子中 switch-case 严格相等检查似乎失败了?
- javascript - HTML 中的无效字符阻止我的菜单切换?
- c++ - 如果没有子字符串,这是打破 while 循环的好方法吗?
- angular - 表单验证无法正常工作