案例陈述不起作用 - VBA

Question

在我的项目中，我有一个客户列表，这些客户具有独特的佣金计算(例如Client_1的佣金率为0.1%，而Client_2的佣金率为0.25%(。

在我的 VBA 代码中，我制作了特殊情况，这些情况因client_id而异。我使用此代码遇到的问题是，即使我输入了一个特殊的 ID，它也会跳过大小写(例如，如果 klienta_nr = 100，它会跳过大小写 klienta_nr = 100 并返回 Case Else(。如何解决此问题？

Private Sub CommandButton1_Click()
Dim klienta_nr As Long
Dim ISIN As String
Dim Cena As Double
Dim Skaits As Double
Dim Komisija As Double
Dim vk As String
Set kSheet = ThisWorkbook.Sheets("komisijas")

klienta_nr = Range("B2").Value
ISIN = Range("E2").Value
Cena = Range("H2").Value
Skaits = Range("I2").Value
vk = Range("B2").Value
Select Case klienta_nr
Case klienta_nr = 100
If klienta_nr = 100 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
Komisija = (Cena * Skaits) * 0.01
Range("K2").Value = Komisija
End If
If klienta_nr = 100 And Komisija <= 30 Then
Range("K2").Value = 30
End If
'Case where klient is special, but ISIN doesn't apply
If klienta_nr = 100 And (Left(ISIN, 2) <> "DE" Or Left(ISIN, 2) <> "FR" Or Left(ISIN, 2) <> "NL" Or Left(ISIN, 2) <> "IT" Or Left(ISIN, 2) <> "IE") Then
Komisija = (Cena * Skaits) * 0.003
If Komisija >= 40 Then
Range("K2").Value = 40
End If
End If

Case klienta_nr = 105
If klienta_nr = 105 And (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
Komisija = (Cena * Skaits) * 0.01
Range("K2").Value = Komisija
End If
'Set 30 EUR Min
If klienta_nr = 105 And Komisija <= 30 Then
Range("K2").Value = 30
End If
'End If

Case klienta_nr = 110
If klienta_nr = 110 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
If klienta_nr = 110 And (Left(ISIN, 2) = "US") Then
Komisija = (Cena * Skaits) * 0.002
End If
If klienta_nr = 110 And (Left(ISIN, 2) = "UK") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
If klienta_nr = 110 And (Left(ISIN, 2) = "CH") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
'Set 20 [valūte] MIN
If klienta_nr = 110 And Komisija <= 20 Then
Range("K2").Value = 20
End If

Case klienta_nr = 115
If klienta_nr = 115 And (Left(ISIN, 2) = "NO" Or Left(ISIN, 2) = "SE" Or Left(ISIN, 2) = "DK" Or Left(ISIN, 2) = "FI" Or Left(ISIN, 2) = "IS" Or Left(ISIN, 2) = "LT" Or Left(ISIN, 2) = "EE" Or Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE" Or Left(ISIN, 2) = "AT" Or Left(ISIN, 2) = "BE" Or Left(ISIN, 2) = "ES" Or Left(ISIN, 2) = "PT") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
If klienta_nr = 115 And (Left(ISIN, 2) = "US") Then
Komisija = (Cena * Skaits) * 0.002
End If
If klienta_nr = 115 And (Left(ISIN, 2) = "UK") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
If klienta_nr = 115 And (Left(ISIN, 2) = "CH") Then
Komisija = (Cena * Skaits) * 0.002
Range("K2").Value = Komisija
End If
'Set 20 [valūte] MIN
If klienta_nr = 115 And Komisija <= 20 Then
Range("K2").Value = 20
End If

Case klienta_nr = 120
If klienta_nr = 120 And (Left(ISIN, 2) = "US") Then
Komisija = (Cena * Skaits) * 0.0027
Range("K2").Value = Komisija
End If
'Set 40 USD MIN
If klienta_nr = 120 And Komisija <= 40 Then
Range("K2").Value = 40
End If

'Non-special klient cases
Case Else
If Not Application.Match(klienta_nr, kSheet.Range("A2:A100")) Then
If Right(vk, 1) = 1 Or Right(vk, 1) = 8 Then
Komisija = (Cena * Skaits) * 0.003
Range("K2").Value = Komisija
End If
If Right(vk, 1) = 7 Then
Komisija = (Cena * Skaits) * 0.01
Range("K2").Value = Komisija
End If
'Komisija MAX is 40, so anything >=40 equals 40
If Komisija >= 40 Then
Range("K2").Value = 40
End If
End If
End Select               
End Sub

Answer 1

选择案例只需要条件

Select Case klienta_nr

而不是在案例部分，如下所示：

改变

Case klienta_nr = 105

自

Case 105

Answer 2

一旦你有了你的Select Case klienta_nr，然后第一种情况是Case 100，你不要在If klienta_nr = 100下面重复，因为你已经在Case中检查过了。

将Select Case结构更改为：

Select Case klienta_nr
Case 100
If (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
Komisija = (Cena * Skaits) * 0.01
Range("K2").Value = Komisija
End If
If Komisija <= 30 Then
Range("K2").Value = 30
End If
'Case where klient is special, but ISIN doesn't apply
If (Left(ISIN, 2) <> "DE" Or Left(ISIN, 2) <> "FR" Or Left(ISIN, 2) <> "NL" Or Left(ISIN, 2) <> "IT" Or Left(ISIN, 2) <> "IE") Then
Komisija = (Cena * Skaits) * 0.003
If Komisija >= 40 Then
Range("K2").Value = 40
End If
End If
Case 105
If (Left(ISIN, 2) = "DE" Or Left(ISIN, 2) = "FR" Or Left(ISIN, 2) = "NL" Or Left(ISIN, 2) = "IT" Or Left(ISIN, 2) = "IE") Then
Komisija = (Cena * Skaits) * 0.01
Range("K2").Value = Komisija
End If
'Set 30 EUR Min
If Komisija <= 30 Then
Range("K2").Value = 30
End If
' add here your other cases...

End Select