案例陈述不起作用-VBA [英] Case Statement Not Working - VBA
问题描述
在我的项目中,我列出了具有唯一佣金计算的客户列表(例如,Client_1的佣金率为0.1%,而Client_2的佣金率为0.25%).
In my project I have a list of clients that have unique commission calculations (ex. Client_1 has a 0.1% commission rate, whereas Client_2 has a 0.25% commission rate).
在我的VBA代码中,我做了一些特殊情况,具体情况取决于client_id.我在这段代码中遇到的问题是,即使我输入了特殊的ID,它也会跳过大小写(例如,如果klienta_nr = 100,它将跳过Case klienta_nr = 100并返回"Case Else").我该如何解决这个问题?
In my VBA code I made special cases, that vary by the client_id. The problem I'm getting with this code is that even though I put in a special ID, it jumps over the case (ex. if klienta_nr = 100, it skips over the Case klienta_nr = 100 and returns the Case Else). How can I fix this issue?
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
推荐答案
Select Case klienta_nr
并且不在案例"部分中,如下所示:
And not in the Case part as below:
更改
Case klienta_nr = 105
到
Case 105
这篇关于案例陈述不起作用-VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!